package a_aa_amainpackage.command_deal.dadanpin0403

import com.alibaba.fastjson.JSON
import org.apache.spark.sql.SQLContext
import org.apache.spark.sql.functions._
import org.apache.spark.{SparkConf, SparkContext}
/*
*项目名: o2odata_commend
*文件名: ClearText
*创建者: hancongcong
*创建时间:2020/3/24 10:12
*描述: TODO
*/ object ClearText {
  def json_analysis(js: String, exp: String): String = {
    var ge = "-1"
    try {
      val st = JSON.parseObject(js)
      //      val nObject = JSON.parseObject(js)
      try {
        ge = st.getString(exp)
      } catch {
        case ex: Exception => ge.toString()
      }
    } catch {
      case ex: Exception => ge.toString()
    }
    return ge
  }

  def main(args: Array[String]): Unit = {

    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
//    conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.29")
//    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
//    sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")
    sqlContext.udf.register("json_parse", json_analysis _)

//    val sourcedata =
//      sqlContext.read.orc(s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403/2019/hmxs/1/*",
//      "s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403/2019/suning/1/*")
//      .where("platformId='3'")
//    sourcedata.show()
//    sourcedata.printSchema()
//    sourcedata.where("platformId='3'").show()
//    sourcedata.where("platformId not in ('3') ").show()


    for(year <- Array("2019","2018")){

      all(sqlContext,year)
      pinduoduo(sqlContext,year)
      if(year.equals("2019")){
        hmxs(sqlContext,year)
      }

    }
    sc.stop()
  }

  def all(sqlContext:SQLContext,year:String):Unit = {
    //清洗
    val sourcedata =
      sqlContext.read.orc(s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403_puer/${year}/taobao/*/*",
        s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403_puer/${year}/tmall/*/*",
        s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403_puer/${year}/jd/*/*",
        s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403_puer/${year}/suning/*/*")
          .withColumn("tbCategoryId",lit("-1"))
    //o2o_ddp_ODS
    sourcedata.registerTempTable("o2o_ddp_ODS")
    val result = sqlContext.sql(
      """
        |select
        |*,
        |case
        |when danpin_firstCate = '苹果' then
        |(case
        |when base_secondcate like '%加力%'   then             '加力'
        |when base_secondcate like '%爱妃%'   then             '爱妃'
        |when base_secondcate like '%青苹果%'   then           '青苹果'
        |when base_secondcate like '%蛇果%'   then             '蛇果'
        |when base_secondcate like '%冰糖心%'   then           '冰糖心'
        |when base_secondcate like '%红富士%'   then           '红富士'
        |when base_secondcate like '%花牛%'   then             '花牛'
        |when base_secondcate like '%黄元帅%'   then           '黄元帅'
        |when base_secondcate like '%黄香蕉%'   then           '黄香蕉'
        |when base_secondcate like '%嘎拉%'   then             '嘎拉'
        |when base_secondcate like '%金冠%'   then             '金冠'
        |when base_secondcate like '%红星%'   then             '红星'
        |when base_secondcate like '%乔纳金%'   then           '乔纳金'
        |when base_secondcate like '%澳洲青果%'   then         '澳洲青果'
        |when base_secondcate like '%国光%'   then             '国光'
        |else (
        |case
        |when title like '%加力%'   then             '加力'
        |when title like '%爱妃%'   then             '爱妃'
        |when title like '%青苹果%'   then           '青苹果'
        |when title like '%蛇果%'   then             '蛇果'
        |when title like '%冰糖心%'   then           '冰糖心'
        |when title like '%红富士%'   then           '红富士'
        |when title like '%花牛%'   then             '花牛'
        |when title like '%黄元帅%'   then           '黄元帅'
        |when title like '%黄香蕉%'   then           '黄香蕉'
        |when title like '%嘎拉%'   then             '嘎拉'
        |when title like '%金冠%'   then             '金冠'
        |when title like '%红星%'   then             '红星'
        |when title like '%乔纳金%'   then           '乔纳金'
        |when title like '%澳洲青果%'   then         '澳洲青果'
        |when title like '%国光%'   then             '国光'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '橙子/橘子' then
        |(case
        |when base_secondcate like      '%南非橙%'          then      '南非橙'
        |when base_secondcate like      '%西班牙橙%'          then    '西班牙橙'
        |when base_secondcate like      '%绿橙%'            then     '绿橙'
        |when base_secondcate like      '%澳洲橙%'          then      '澳洲橙'
        |when base_secondcate like      '%新奇士%'            then    '新奇士'
        |when base_secondcate like      '%埃及橙%'            then    '埃及橙'
        |when base_secondcate like      '%夏橙%'          then       '夏橙'
        |when base_secondcate like      '%伦晚橙%'            then    '伦晚橙'
        |when base_secondcate like      '%茂古柑%'            then    '茂古柑'
        |when base_secondcate like      '%柳橙%'          then       '柳橙'
        |when base_secondcate like      '%皇帝柑%'          then      '皇帝柑'
        |when base_secondcate like      '%青见%'          then       '青见'
        |when base_secondcate like      '%贡柑%'            then     '贡柑'
        |when base_secondcate like      '%桔子%'             then    '桔子'
        |when base_secondcate like      '%芦柑%'          then       '芦柑'
        |when base_secondcate like      '%茂谷柑%'          then      '茂谷柑'
        |when base_secondcate like      '%金桔%'          then       '金桔'
        |when base_secondcate like      '%春见%'          then       '春见'
        |when base_secondcate like      '%丑柑%'          then       '丑柑'
        |when base_secondcate like      '%椪柑%'          then       '椪柑'
        |when base_secondcate like      '%蜜桔%'          then       '蜜桔'
        |when base_secondcate like      '%砂糖橘%'          then      '砂糖橘'
        |when base_secondcate like      '%不知火%'          then      '不知火'
        |else (
        |case
        |when title like      '%南非橙%'          then      '南非橙'
        |when title like      '%西班牙橙%'          then    '西班牙橙'
        |when title like      '%绿橙%'            then     '绿橙'
        |when title like      '%澳洲橙%'          then      '澳洲橙'
        |when title like      '%新奇士%'            then    '新奇士'
        |when title like      '%埃及橙%'            then    '埃及橙'
        |when title like      '%夏橙%'          then       '夏橙'
        |when title like      '%伦晚橙%'            then    '伦晚橙'
        |when title like      '%茂古柑%'            then    '茂古柑'
        |when title like      '%柳橙%'          then       '柳橙'
        |when title like      '%皇帝柑%'          then      '皇帝柑'
        |when title like      '%青见%'          then       '青见'
        |when title like      '%贡柑%'            then     '贡柑'
        |when title like      '%桔子%'             then    '桔子'
        |when title like      '%芦柑%'          then       '芦柑'
        |when title like      '%茂谷柑%'          then      '茂谷柑'
        |when title like      '%金桔%'          then       '金桔'
        |when title like      '%春见%'          then       '春见'
        |when title like      '%丑柑%'          then       '丑柑'
        |when title like      '%椪柑%'          then       '椪柑'
        |when title like      '%蜜桔%'          then       '蜜桔'
        |when title like      '%砂糖橘%'          then      '砂糖橘'
        |when title like      '%不知火%'          then      '不知火'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '车厘子/樱桃' then
        |(case
        |when base_secondcate like     '%美早%'          then    '美早'
        |when base_secondcate like     '%黑珍珠%'         then   '黑珍珠'
        |when base_secondcate like     '%大红灯%'        then    '大红灯'
        |when base_secondcate like     '%宾莹%'       then       '宾莹'
        |when base_secondcate like     '%水晶%'         then     '水晶'
        |when base_secondcate like     '%先锋%'         then     '先锋'
        |when base_secondcate like     '%拉宾斯%'      then      '拉宾斯'
        |when base_secondcate like     '%雷尼尔%'         then   '雷尼尔'
        |when base_secondcate like     '%萨米脱%'         then   '萨米脱'
        |when base_secondcate like     '%科迪亚%'      then      '科迪亚'
        |when base_secondcate like     '%意大利早红%'       then '意大利早红'
        |else (
        |case
        |when title like     '%美早%'          then    '美早'
        |when title like     '%黑珍珠%'         then   '黑珍珠'
        |when title like     '%大红灯%'        then    '大红灯'
        |when title like     '%宾莹%'       then       '宾莹'
        |when title like     '%水晶%'         then     '水晶'
        |when title like     '%先锋%'         then     '先锋'
        |when title like     '%拉宾斯%'      then      '拉宾斯'
        |when title like     '%雷尼尔%'         then   '雷尼尔'
        |when title like     '%萨米脱%'         then   '萨米脱'
        |when title like     '%科迪亚%'      then      '科迪亚'
        |when title like     '%意大利早红%'       then '意大利早红'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '小龙虾' then
        |(case
        |when base_secondcate like    '%虾类制品%'     then '虾类制品'
        |when base_secondcate like    '%活虾%'      then   '活虾'
        |when base_secondcate like    '%虾仁%'     then    '虾仁'
        |when base_secondcate like    '%虾米%'  then       '虾米'
        |when base_secondcate like    '%冻虾%'    then     '冻虾'
        |else (
        |case
        |when title like    '%虾类制品%'     then '虾类制品'
        |when title like    '%活虾%'      then   '活虾'
        |when title like    '%虾仁%'     then    '虾仁'
        |when title like    '%虾米%'  then       '虾米'
        |when title like    '%冻虾%'    then     '冻虾'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '蟹类' then
        |(case
        |when base_secondcate like    '%鲜活%'     then '鲜活'
        |when base_secondcate like    '%冷冻%'      then   '冷冻'
        |when base_secondcate like    '%冷藏%'     then    '冷藏'
        |else (
        |case
        |when title like    '%鲜活%'     then '鲜活'
        |when title like    '%冷冻%'      then   '冷冻'
        |when title like    '%冷藏%'     then    '冷藏'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('大米','杂粮') then
        |(case
        |when base_secondcate like   '%杂粮礼盒%'     then '杂粮礼盒'
        |when base_secondcate like   '%糙米%'      then   '糙米'
        |when base_secondcate like   '%小米%'     then    '小米'
        |when base_secondcate like   '%黑豆%'     then    '黑豆'
        |when base_secondcate like   '%黄豆%'      then   '黄豆'
        |when base_secondcate like   '%黑米%'     then    '黑米'
        |when base_secondcate like   '%薏米%'     then    '薏米'
        |when base_secondcate like   '%糯米%'      then   '糯米'
        |else (
        |case
        |when title like   '%杂粮礼盒%'     then '杂粮礼盒'
        |when title like   '%糙米%'      then   '糙米'
        |when title like   '%小米%'     then    '小米'
        |when title like   '%黑豆%'     then    '黑豆'
        |when title like   '%黄豆%'      then   '黄豆'
        |when title like   '%黑米%'     then    '黑米'
        |when title like   '%薏米%'     then    '薏米'
        |when title like   '%糯米%'      then   '糯米'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('红茶') then
        |(case
        |when base_secondcate like  '%正山小种%'     then     '正山小种'
        |when base_secondcate like  '%金骏眉%'      then     '金骏眉'
        |when base_secondcate like  '%滇红%'        then     '滇红'
        |when base_secondcate like  '%祁门%'       then      '祁门'
        |when base_secondcate like  '%再加工茶%'   then       '再加工茶'
        |else (
        |case
        |when title like  '%正山小种%'     then     '正山小种'
        |when title like  '%金骏眉%'      then     '金骏眉'
        |when title like  '%滇红%'        then     '滇红'
        |when title like  '%祁门%'       then      '祁门'
        |when title like  '%再加工茶%'   then       '再加工茶'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('绿茶') then
        |(case
        |when base_secondcate like  '%洞庭碧螺春%'    then     '洞庭碧螺春'
        |when base_secondcate like  '%西湖龙井%'    then      '西湖龙井'
        |when base_secondcate like  '%信阳毛尖%'    then      '信阳毛尖'
        |when base_secondcate like  '%日照绿茶%'   then       '日照绿茶'
        |when base_secondcate like  '%龙井%'  then            '龙井'
        |when base_secondcate like  '%竹叶青%'    then        '竹叶青'
        |when base_secondcate like  '%雀舌%'    then          '雀舌'
        |when base_secondcate like  '%黄山毛峰%'    then      '黄山毛峰'
        |when base_secondcate like  '%毛峰%'   then           '毛峰'
        |else (
        |case
        |when title like  '%洞庭碧螺春%'    then     '洞庭碧螺春'
        |when title like  '%西湖龙井%'    then      '西湖龙井'
        |when title like  '%信阳毛尖%'    then      '信阳毛尖'
        |when title like  '%日照绿茶%'   then       '日照绿茶'
        |when title like  '%龙井%'  then            '龙井'
        |when title like  '%竹叶青%'    then        '竹叶青'
        |when title like  '%雀舌%'    then          '雀舌'
        |when title like  '%黄山毛峰%'    then      '黄山毛峰'
        |when title like  '%毛峰%'   then           '毛峰'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('黑茶') then
        |(case
        |when base_secondcate like  '%黑茶%'      then     '黑茶'
        |when base_secondcate like  '%普洱%'     then    '普洱茶'
        |when base_secondcate like  '%老青茶%'     then    '老青茶'
        |when base_secondcate like  '%六堡茶%'    then     '六堡茶'
        |when base_secondcate like  '%藏茶%'   then        '藏茶'
        |else (
        |case
        |when title like  '%黑茶%'      then     '黑茶'
        |when title like  '%普洱%'     then    '普洱茶'
        |when title like  '%老青茶%'     then    '老青茶'
        |when title like  '%六堡茶%'    then     '六堡茶'
        |when title like  '%藏茶%'   then        '藏茶'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('蜜饯干果') then
        |(case
        |when base_secondcate like  '%梅类%'    then    '梅类'
        |when base_secondcate like  '%芒果干%'     then '芒果干'
        |when base_secondcate like  '%橄榄%'     then   '橄榄'
        |when base_secondcate like  '%葡萄干%'    then  '葡萄干'
        |when base_secondcate like  '%金桔%' then       '金桔'
        |when base_secondcate like  '%桃干%'    then    '桃干'
        |when base_secondcate like  '%草莓干%'     then '草莓干'
        |when base_secondcate like  '%无花果干%'     then '无花果干'
        |when base_secondcate like  '%李子%'    then    '李子'
        |when base_secondcate like  '%菠萝蜜%' then     '菠萝蜜'
        |else (
        |case
        |when title like  '%梅类%'    then    '梅类'
        |when title like  '%芒果干%'     then '芒果干'
        |when title like  '%橄榄%'     then   '橄榄'
        |when title like  '%葡萄干%'    then  '葡萄干'
        |when title like  '%金桔%' then       '金桔'
        |when title like  '%桃干%'    then    '桃干'
        |when title like  '%草莓干%'     then '草莓干'
        |when title like  '%无花果干%'     then '无花果干'
        |when title like  '%李子%'    then    '李子'
        |when title like  '%菠萝蜜%' then     '菠萝蜜'
        |else '其他' end
        |)end
        |)
        |
        |else '其他' end as danpin_secondCate
        |from
        |(select *,
        |case
        |when chandi like '湖南%'              then '湖南省'
        |when chandi like '云南%'              then '云南省'
        |when chandi like '河南%'              then '河南省'
        |when chandi like '江苏%'              then '江苏省'
        |when chandi like '甘肃%'              then '甘肃省'
        |when chandi like '黑龙江%'            then '黑龙江省'
        |when chandi like '天津%'              then '天津市'
        |when chandi like '广西壮族%'          then '广西壮族自治区'
        |when chandi like '陕西%'              then '陕西省'
        |when chandi like '宁夏回族%'          then '宁夏回族自治区'
        |when chandi like '湖北%'              then '湖北省'
        |when chandi like '重庆%'              then '重庆市'
        |when chandi like '江西%'              then '江西省'
        |when chandi like '四川%'              then '四川省'
        |when chandi like '山西%'              then '山西省'
        |when chandi like '青海%'              then '青海省'
        |when chandi like '福建%'              then '福建省'
        |when chandi like '河北%'              then '河北省'
        |when chandi like '海南%'              then '海南省'
        |when chandi like '新疆维吾尔%'        then '新疆维吾尔自治区'
        |when chandi like '广东%'              then '广东省'
        |when chandi like '北京%'              then '北京市'
        |when chandi like '辽宁%'              then '辽宁省'
        |when chandi like '安徽%'              then '安徽省'
        |when chandi like '吉林%'              then '吉林省'
        |when chandi like '浙江%'              then '浙江省'
        |when chandi like '西藏%'              then '西藏自治区'
        |when chandi like '内蒙古%'            then '内蒙古自治区'
        |when chandi like '上海%'              then '上海市'
        |when chandi like '山东%'              then '山东省'
        |when chandi like '贵州%'              then '贵州省'
        |else '-1' end as chandi_province,
        |case
        |when chandi1 rlike '中卫'      then '中卫市'
        |when chandi1 rlike '兰州'      then '兰州市'
        |when chandi1 rlike '嘉峪关'    then '嘉峪关市'
        |when chandi1 rlike '克拉玛依'  then '克拉玛依市'
        |when chandi1 rlike '西安'      then '西安市'
        |when chandi1 rlike '酒泉'      then '酒泉市'
        |when chandi1 rlike '伊犁哈萨克'then '伊犁哈萨克自治州'
        |when chandi1 rlike '咸阳'      then '咸阳市'
        |when chandi1 rlike '安康'      then '安康市'
        |when chandi1 rlike '汉中'      then '汉中市'
        |when chandi1 rlike '延安'      then '延安市'
        |when chandi1 rlike '陇南'      then '陇南市'
        |when chandi1 rlike '玉树藏'  then '玉树藏族自治州'
        |when chandi1 rlike '金昌'      then '金昌市'
        |when chandi1 rlike '庆阳'      then '庆阳市'
        |when chandi1 rlike '张掖'      then '张掖市'
        |when chandi1 rlike '渭南'      then '渭南市'
        |when chandi1 rlike '海南藏'  then '海南藏族自治州'
        |when chandi1 rlike '西宁'      then '西宁市'
        |when chandi1 rlike '天水'      then '天水市'
        |when chandi1 rlike '吐鲁番'    then '吐鲁番市'
        |when chandi1 rlike '喀什地'    then '喀什地区'
        |when chandi1 rlike '乌鲁木齐'  then '乌鲁木齐市'
        |when chandi1 rlike '巴音郭楞蒙古' then '巴音郭楞蒙古自治州'
        |when chandi1 rlike '辽源'      then '辽源市'
        |when chandi1 rlike '锦州'      then '锦州市'
        |when chandi1 rlike '大兴安岭地'then '大兴安岭地区'
        |when chandi1 rlike '松原'      then '松原市'
        |when chandi1 rlike '长春'      then '长春市'
        |when chandi1 rlike '伊春'      then '伊春市'
        |when chandi1 rlike '朝阳'      then '朝阳市'
        |when chandi1 rlike '阜新'      then '阜新市'
        |when chandi1 rlike '丹东'      then '丹东市'
        |when chandi1 rlike '双鸭山'    then '双鸭山市'
        |when chandi1 rlike '黑河'      then '黑河市'
        |when chandi1 rlike '大连'      then '大连市'
        |when chandi1 rlike '哈尔滨'    then '哈尔滨市'
        |when chandi1 rlike '四平'      then '四平市'
        |when chandi1 rlike '佳木斯'    then '佳木斯市'
        |when chandi1 rlike '辽阳'      then '辽阳市'
        |when chandi1 rlike '鸡西'      then '鸡西市'
        |when chandi1 rlike '本溪'      then '本溪市'
        |when chandi1 rlike '白山'      then '白山市'
        |when chandi1 rlike '七台河'    then '七台河市'
        |when chandi1 rlike '绥化'      then '绥化市'
        |when chandi1 rlike '吉林'      then '吉林市'
        |when chandi1 rlike '通化'      then '通化市'
        |when chandi1 rlike '遂宁'      then '遂宁市'
        |when chandi1 rlike '长治'      then '长治市'
        |when chandi1 rlike '达州'      then '达州市'
        |when chandi1 rlike '廊坊'      then '廊坊市'
        |when chandi1 rlike '六盘水'    then '六盘水市'
        |when chandi1 rlike '阳泉'      then '阳泉市'
        |when chandi1 rlike '毕节'      then '毕节市'
        |when chandi1 rlike '成都'      then '成都市'
        |when chandi1 rlike '锡林郭勒盟' then '锡林郭勒盟'
        |when chandi1 rlike '衡水'      then '衡水市'
        |when chandi1 rlike '攀枝花'    then '攀枝花市'
        |when chandi1 rlike '凉山彝'  then '凉山彝族自治州'
        |when chandi1 rlike '绵阳'      then '绵阳市'
        |when chandi1 rlike '昆明'      then '昆明市'
        |when chandi1 rlike '雅安'      then '雅安市'
        |when chandi1 rlike '广安'      then '广安市'
        |when chandi1 rlike '邯郸'      then '邯郸市'
        |when chandi1 rlike '天津'      then '天津市'
        |when chandi1 rlike '吕梁'      then '吕梁市'
        |when chandi1 rlike '宜宾'      then '宜宾市'
        |when chandi1 rlike '大同'      then '大同市'
        |when chandi1 rlike '儋州'      then '儋州市'
        |when chandi1 rlike '清远'      then '清远市'
        |when chandi1 rlike '江门'      then '江门市'
        |when chandi1 rlike '河源'      then '河源市'
        |when chandi1 rlike '三亚'      then '三亚市'
        |when chandi1 rlike '玉林'      then '玉林市'
        |when chandi1 rlike '中山'      then '中山市'
        |when chandi1 rlike '贺州'      then '贺州市'
        |when chandi1 rlike '来宾'      then '来宾市'
        |when chandi1 rlike '柳州'      then '柳州市'
        |when chandi1 rlike '佛山'      then '佛山市'
        |when chandi1 rlike '珠海'      then '珠海市'
        |when chandi1 rlike '汕尾'      then '汕尾市'
        |when chandi1 rlike '韶关'      then '韶关市'
        |when chandi1 rlike '东莞'      then '东莞市'
        |when chandi1 rlike '肇庆'      then '肇庆市'
        |when chandi1 rlike '惠州'      then '惠州市'
        |when chandi1 rlike '汕头'      then '汕头市'
        |when chandi1 rlike '河池'      then '河池市'
        |when chandi1 rlike '百色'      then '百色市'
        |when chandi1 rlike '黄冈'      then '黄冈市'
        |when chandi1 rlike '娄底'      then '娄底市'
        |when chandi1 rlike '湘潭'      then '湘潭市'
        |when chandi1 rlike '岳阳'      then '岳阳市'
        |when chandi1 rlike '常德'      then '常德市'
        |when chandi1 rlike '黄石'      then '黄石市'
        |when chandi1 rlike '荆门'      then '荆门市'
        |when chandi1 rlike '武汉'      then '武汉市'
        |when chandi1 rlike '宜昌'      then '宜昌市'
        |when chandi1 rlike '张家界'    then '张家界市'
        |when chandi1 rlike '咸宁'      then '咸宁市'
        |when chandi1 rlike '驻马店'    then '驻马店市'
        |when chandi1 rlike '随州'      then '随州市'
        |when chandi1 rlike '商丘'      then '商丘市'
        |when chandi1 rlike '洛阳'      then '洛阳市'
        |when chandi1 rlike '永州'      then '永州市'
        |when chandi1 rlike '安阳'      then '安阳市'
        |when chandi1 rlike '鹤壁'      then '鹤壁市'
        |when chandi1 rlike '益阳'      then '益阳市'
        |when chandi1 rlike '平顶山'    then '平顶山市'
        |when chandi1 rlike '荆州'      then '荆州市'
        |when chandi1 rlike '株洲'      then '株洲市'
        |when chandi1 rlike '信阳'      then '信阳市'
        |when chandi1 rlike '南阳'      then '南阳市'
        |when chandi1 rlike '濮阳'      then '濮阳市'
        |when chandi1 rlike '开封'      then '开封市'
        |when chandi1 rlike '白银'      then '白银市'
        |when chandi1 rlike '平凉'      then '平凉市'
        |when chandi1 rlike '铜川'      then '铜川市'
        |when chandi1 rlike '阿克苏地'  then '阿克苏地区'
        |when chandi1 rlike '哈密'      then '哈密市'
        |when chandi1 rlike '商洛'      then '商洛市'
        |when chandi1 rlike '宝鸡'      then '宝鸡市'
        |when chandi1 rlike '石嘴山'    then '石嘴山市'
        |when chandi1 rlike '甘南藏'  then '甘南藏族自治州'
        |when chandi1 rlike '武威'      then '武威市'
        |when chandi1 rlike '沈阳'      then '沈阳市'
        |when chandi1 rlike '盘锦'      then '盘锦市'
        |when chandi1 rlike '铁岭'      then '铁岭市'
        |when chandi1 rlike '齐齐哈尔'  then '齐齐哈尔市'
        |when chandi1 rlike '牡丹江'    then '牡丹江市'
        |when chandi1 rlike '营口'      then '营口市'
        |when chandi1 rlike '大庆'      then '大庆市'
        |when chandi1 rlike '抚顺'      then '抚顺市'
        |when chandi1 rlike '运城'      then '运城市'
        |when chandi1 rlike '沧州'      then '沧州市'
        |when chandi1 rlike '黔南布依族苗' then '黔南布依族苗族自治州'
        |when chandi1 rlike '红河哈尼族彝' then '红河哈尼族彝族自治州'
        |when chandi1 rlike '楚雄彝'  then '楚雄彝族自治州'
        |when chandi1 rlike '门头沟'    then '门头沟区'
        |when chandi1 rlike '呼和浩特'  then '呼和浩特市'
        |when chandi1 rlike '保定'      then '保定市'
        |when chandi1 rlike '眉山'      then '眉山市'
        |when chandi1 rlike '重庆'      then '重庆市'
        |when chandi1 rlike '铜仁'      then '铜仁市'
        |when chandi1 rlike '自贡'      then '自贡市'
        |when chandi1 rlike '巴中'      then '巴中市'
        |when chandi1 rlike '广元'      then '广元市'
        |when chandi1 rlike '晋城'      then '晋城市'
        |when chandi1 rlike '揭阳'      then '揭阳市'
        |when chandi1 rlike '钦州'      then '钦州市'
        |when chandi1 rlike '海口'      then '海口市'
        |when chandi1 rlike '湛江'      then '湛江市'
        |when chandi1 rlike '深圳'      then '深圳市'
        |when chandi1 rlike '潮州'      then '潮州市'
        |when chandi1 rlike '三沙'      then '三沙市'
        |when chandi1 rlike '云浮'      then '云浮市'
        |when chandi1 rlike '茂名'      then '茂名市'
        |when chandi1 rlike '周口'      then '周口市'
        |when chandi1 rlike '郑州'      then '郑州市'
        |when chandi1 rlike '许昌'      then '许昌市'
        |when chandi1 rlike '三门峡'    then '三门峡市'
        |when chandi1 rlike '怀化'      then '怀化市'
        |when chandi1 rlike '襄阳'      then '襄阳市'
        |when chandi1 rlike '衡阳'      then '衡阳市'
        |when chandi1 rlike '漯河'      then '漯河市'
        |when chandi1 rlike '十堰'      then '十堰市'
        |when chandi1 rlike '郴州'      then '郴州市'
        |when chandi1 rlike '马鞍山'    then '马鞍山市'
        |when chandi1 rlike '烟台'      then '烟台市'
        |when chandi1 rlike '九江'      then '九江市'
        |when chandi1 rlike '龙岩'      then '龙岩市'
        |when chandi1 rlike '聊城'      then '聊城市'
        |when chandi1 rlike '济宁'      then '济宁市'
        |when chandi1 rlike '常州'      then '常州市'
        |when chandi1 rlike '宁德'      then '宁德市'
        |when chandi1 rlike '德州'      then '德州市'
        |when chandi1 rlike '临沂'      then '临沂市'
        |when chandi1 rlike '青岛'      then '青岛市'
        |when chandi1 rlike '新余'      then '新余市'
        |when chandi1 rlike '厦门'      then '厦门市'
        |when chandi1 rlike '滁州'      then '滁州市'
        |when chandi1 rlike '苏州'      then '苏州市'
        |when chandi1 rlike '南昌'      then '南昌市'
        |when chandi1 rlike '滨州'      then '滨州市'
        |when chandi1 rlike '南平'      then '南平市'
        |when chandi1 rlike '宁波'      then '宁波市'
        |when chandi1 rlike '淄博'      then '淄博市'
        |when chandi1 rlike '菏泽'      then '菏泽市'
        |when chandi1 rlike '徐州'      then '徐州市'
        |when chandi1 rlike '景德镇'    then '景德镇市'
        |when chandi1 rlike '莆田'      then '莆田市'
        |when chandi1 rlike '台州'      then '台州市'
        |when chandi1 rlike '福州'      then '福州市'
        |when chandi1 rlike '漳州'      then '漳州市'
        |when chandi1 rlike '上饶'      then '上饶市'
        |when chandi1 rlike '济南'      then '济南市'
        |when chandi1 rlike '泉州'      then '泉州市'
        |when chandi1 rlike '嘉兴'      then '嘉兴市'
        |when chandi1 rlike '连云港'    then '连云港市'
        |when chandi1 rlike '金华'      then '金华市'
        |when chandi1 rlike '淮北'      then '淮北市'
        |when chandi1 rlike '上海'      then '上海市'
        |when chandi1 rlike '威海'      then '威海市'
        |when chandi1 rlike '东营'      then '东营市'
        |when chandi1 rlike '蚌埠'      then '蚌埠市'
        |when chandi1 rlike '日照'      then '日照市'
        |when chandi1 rlike '淮安'      then '淮安市'
        |when chandi1 rlike '黄山'      then '黄山市'
        |when chandi1 rlike '焦作'      then '焦作市'
        |when chandi1 rlike '恩施土家族苗' then '恩施土家族苗族自治州'
        |when chandi1 rlike '邵阳'      then '邵阳市'
        |when chandi1 rlike '海东'      then '海东市'
        |when chandi1 rlike '定西'      then '定西市'
        |when chandi1 rlike '黄南藏'  then '黄南藏族自治州'
        |when chandi1 rlike '固原'      then '固原市'
        |when chandi1 rlike '新乡'      then '新乡市'
        |when chandi1 rlike '鄂州'      then '鄂州市'
        |when chandi1 rlike '榆林'      then '榆林市'
        |when chandi1 rlike '银川'      then '银川市'
        |when chandi1 rlike '海西蒙古族藏' then '海西蒙古族藏族自治州'
        |when chandi1 rlike '塔城地'    then '塔城地区'
        |when chandi1 rlike '鞍山'      then '鞍山市'
        |when chandi1 rlike '延边朝鲜' then '延边朝鲜族自治州'
        |when chandi1 rlike '阿勒泰地'  then '阿勒泰地区'
        |when chandi1 rlike '博尔塔拉蒙古' then '博尔塔拉蒙古自治州'
        |when chandi1 rlike '吴忠'      then '吴忠市'
        |when chandi1 rlike '果洛藏'  then '果洛藏族自治州'
        |when chandi1 rlike '内江'      then '内江市'
        |when chandi1 rlike '泸州'      then '泸州市'
        |when chandi1 rlike '朔州'      then '朔州市'
        |when chandi1 rlike '张家口'    then '张家口市'
        |when chandi1 rlike '玉溪'      then '玉溪市'
        |when chandi1 rlike '北京'      then '北京市'
        |when chandi1 rlike '邢台'      then '邢台市'
        |when chandi1 rlike '兴安盟'    then '兴安盟'
        |when chandi1 rlike '临汾'      then '临汾市'
        |when chandi1 rlike '丽江'      then '丽江市'
        |when chandi1 rlike '梅州'      then '梅州市'
        |when chandi1 rlike '贵港'      then '贵港市'
        |when chandi1 rlike '崇左'      then '崇左市'
        |when chandi1 rlike '阳江'      then '阳江市'
        |when chandi1 rlike '广州'      then '广州市'
        |when chandi1 rlike '南宁'      then '南宁市'
        |when chandi1 rlike '孝感'      then '孝感市'
        |when chandi1 rlike '长沙'      then '长沙市'
        |when chandi1 rlike '昌吉回'  then '昌吉回族自治州'
        |when chandi1 rlike '北海'      then '北海市'
        |when chandi1 rlike '桂林'      then '桂林市'
        |when chandi1 rlike '梧州'      then '梧州市'
        |when chandi1 rlike '葫芦岛'    then '葫芦岛市'
        |when chandi1 rlike '唐山'      then '唐山市'
        |when chandi1 rlike '资阳'      then '资阳市'
        |when chandi1 rlike '那曲'      then '那曲市'
        |when chandi1 rlike '秦皇岛'    then '秦皇岛市'
        |when chandi1 rlike '鄂尔多斯'  then '鄂尔多斯市'
        |when chandi1 rlike '承德'      then '承德市'
        |when chandi1 rlike '昭通'      then '昭通市'
        |when chandi1 rlike '忻州'      then '忻州市'
        |when chandi1 rlike '安顺'      then '安顺市'
        |when chandi1 rlike '湘西土家族苗' then '湘西土家族苗族自治州'
        |when chandi1 rlike '白城'      then '白城市'
        |when chandi1 rlike '保山'      then '保山市'
        |when chandi1 rlike '乐山'      then '乐山市'
        |when chandi1 rlike '临沧'      then '临沧市'
        |when chandi1 rlike '南充'      then '南充市'
        |when chandi1 rlike '甘孜藏'  then '甘孜藏族自治州'
        |when chandi1 rlike '和田地'    then '和田地区'
        |when chandi1 rlike '太原'      then '太原市'
        |when chandi1 rlike '静海'      then '静海区'
        |when chandi1 rlike '呼伦贝尔'  then '呼伦贝尔市'
        |when chandi1 rlike '山南'      then '山南市'
        |when chandi1 rlike '合川'      then '合川区'
        |when chandi1 rlike '黔东南苗族侗' then '黔东南苗族侗族自治州'
        |when chandi1 rlike '赤峰'      then '赤峰市'
        |when chandi1 rlike '贵阳'      then '贵阳市'
        |when chandi1 rlike '防城港'    then '防城港市'
        |when chandi1 rlike '鹤岗'      then '鹤岗市'
        |when chandi1 rlike '石家庄'    then '石家庄市'
        |when chandi1 rlike '德阳'      then '德阳市'
        |when chandi1 rlike '遵义'      then '遵义市'
        |when chandi1 rlike '海北藏'  then '海北藏族自治州'
        |when chandi1 rlike '晋中'      then '晋中市'
        |when chandi1 rlike '阿拉善盟'  then '阿拉善盟'
        |when chandi1 rlike '涪陵'      then '涪陵区'
        |when chandi1 rlike '日喀则'    then '日喀则市'
        |when chandi1 rlike '酉阳土家族苗' then '酉阳土家族苗族自治县'
        |when chandi1 rlike '怒江傈僳' then '怒江傈僳族自治州'
        |when chandi1 rlike '乌海'      then '乌海市'
        |when chandi1 rlike '巴彦淖尔'  then '巴彦淖尔市'
        |when chandi1 rlike '南京'      then '南京市'
        |when chandi1 rlike '三明'      then '三明市'
        |when chandi1 rlike '宣城'      then '宣城市'
        |when chandi1 rlike '湖州'      then '湖州市'
        |when chandi1 rlike '枣庄'      then '枣庄市'
        |when chandi1 rlike '抚州'      then '抚州市'
        |when chandi1 rlike '宿州'      then '宿州市'
        |when chandi1 rlike '安庆'      then '安庆市'
        |when chandi1 rlike '衢州'      then '衢州市'
        |when chandi1 rlike '西双版纳傣' then '西双版纳傣族自治州'
        |when chandi1 rlike '绍兴'      then '绍兴市'
        |when chandi1 rlike '萍乡'      then '萍乡市'
        |when chandi1 rlike '吉安'      then '吉安市'
        |when chandi1 rlike '泰安'      then '泰安市'
        |when chandi1 rlike '丽水'      then '丽水市'
        |when chandi1 rlike '宿迁'      then '宿迁市'
        |when chandi1 rlike '临夏回'  then '临夏回族自治州'
        |when chandi1 rlike '北辰'      then '北辰区'
        |when chandi1 rlike '武隆县'    then '武隆县'
        |when chandi1 rlike '拉萨'      then '拉萨市'
        |when chandi1 rlike '南川'      then '南川区'
        |when chandi1 rlike '红桥'      then '红桥区'
        |when chandi1 rlike '扬州'      then '扬州市'
        |when chandi1 rlike '温州'      then '温州市'
        |when chandi1 rlike '阜阳'      then '阜阳市'
        |when chandi1 rlike '亳州'      then '亳州市'
        |when chandi1 rlike '包头'      then '包头市'
        |when chandi1 rlike '巫溪县'    then '巫溪县'
        |when chandi1 rlike '普洱'      then '普洱市'
        |when chandi1 rlike '那曲地'    then '那曲地区'
        |when chandi1 rlike '盐城'      then '盐城市'
        |when chandi1 rlike '合肥'      then '合肥市'
        |when chandi1 rlike '泰州'      then '泰州市'
        |when chandi1 rlike '淮南'      then '淮南市'
        |when chandi1 rlike '曲靖'      then '曲靖市'
        |when chandi1 rlike '铜陵'      then '铜陵市'
        |when chandi1 rlike '赣州'      then '赣州市'
        |when chandi1 rlike '静安'      then '静安区'
        |when chandi1 rlike '通辽'      then '通辽市'
        |when chandi1 rlike '芜湖'      then '芜湖市'
        |when chandi1 rlike '杭州'      then '杭州市'
        |when chandi1 rlike '武清'      then '武清区'
        |when chandi1 rlike '南岸'      then '南岸区'
        |when chandi1 rlike '海淀'      then '海淀区'
        |when chandi1 rlike '九龙坡'    then '九龙坡区'
        |when chandi1 rlike '镇江'      then '镇江市'
        |when chandi1 rlike '潍坊'      then '潍坊市'
        |when chandi1 rlike '长寿'      then '长寿区'
        |when chandi1 rlike '无锡'      then '无锡市'
        |when chandi1 rlike '池州'      then '池州市'
        |when chandi1 rlike '鹰潭'      then '鹰潭市'
        |when chandi1 rlike '六安'      then '六安市'
        |when chandi1 rlike '宜春'      then '宜春市'
        |when chandi1 rlike '南通'      then '南通市'
        |when chandi1 rlike '荣昌'      then '荣昌区'
        |when chandi1 rlike '昌都'      then '昌都市'
        |when chandi1 rlike '大理白'  then '大理白族自治州'
        |when chandi1 rlike '河西'      then '河西区'
        |when chandi1 rlike '舟山'      then '舟山市'
        |when chandi1 rlike '克孜勒苏柯尔克孜' then '克孜勒苏柯尔克孜自治州'
        |when chandi1 rlike '秀山土家族苗'then '秀山土家族苗族自治县'
        |when chandi1 rlike '黔西南布依族苗' then '黔西南布依族苗族自治州'
        |when chandi1 rlike '璧山'      then '璧山区'
        |when chandi1 rlike '东丽'      then '东丽区'
        |when chandi1 rlike '密云'      then '密云区'
        |when chandi1 rlike '沙坪坝'    then '沙坪坝区'
        |when chandi1 rlike '北碚'      then '北碚区'
        |when chandi1 rlike '延庆'      then '延庆区'
        |when chandi1 rlike '乌兰察布'  then '乌兰察布市'
        |when chandi1 rlike '阿里地'    then '阿里地区'
        |when chandi1 rlike '房山'      then '房山区'
        |when chandi1 rlike '蓟州'      then '蓟州区'
        |when chandi1 rlike '昌平'      then '昌平区'
        |when chandi1 rlike '渝北'      then '渝北区'
        |when chandi1 rlike '巴南'      then '巴南区'
        |when chandi1 rlike '顺义'      then '顺义区'
        |when chandi1 rlike '城口县'    then '城口县'
        |when chandi1 rlike '潼南'      then '潼南区'
        |when chandi1 rlike '垫江县'    then '垫江县'
        |when chandi1 rlike '江津'      then '江津区'
        |when chandi1 rlike '怀柔'      then '怀柔区'
        |when chandi1 rlike '平谷'      then '平谷区'
        |when chandi1 rlike '忠县'      then '忠县'
        |when chandi1 rlike '西青'      then '西青区'
        |when chandi1 rlike '大足'      then '大足区'
        |when chandi1 rlike '林芝'      then '林芝市'
        |when chandi1 rlike '南开'      then '南开区'
        |when chandi1 rlike '通州'      then '通州区'
        |when chandi1 rlike '文山壮族苗' then '文山壮族苗族自治州'
        |when chandi1 rlike '滨海新'    then '滨海新区'
        |when chandi1 rlike '大兴'      then '大兴区'
        |when chandi1 rlike '德宏傣族景颇' then '德宏傣族景颇族自治州'
        |when chandi1 rlike '綦江'      then '綦江区'
        |when chandi1 rlike '丰台'      then '丰台区'
        |when chandi1 rlike '渝中'      then '渝中区'
        |when chandi1 rlike '黔江'      then '黔江区'
        |when chandi1 rlike '东城'      then '东城区'
        |when chandi1 rlike '云阳县'    then '云阳县'
        |when chandi1 rlike '彭水苗族土家'   then '彭水苗族土家族自治县'
        |when chandi1 rlike '巫山县'    then '巫山县'
        |when chandi1 rlike '河东'      then '河东区'
        |when chandi1 rlike '宝坻'      then '宝坻区'
        |when chandi1 rlike '铜梁'      then '铜梁区'
        |when chandi1 rlike '津南'      then '津南区'
        |when chandi1 rlike '丰都县'    then '丰都县'
        |when chandi1 rlike '阿坝藏族羌' then '阿坝藏族羌族自治州'
        |when chandi1 rlike '西城'      then '西城区'
        |when chandi1 rlike '宁河'      then '宁河区'
        |when chandi1 rlike '大渡口'    then '大渡口区'
        |when chandi1 rlike '永川'      then '永川区'
        |when chandi1 rlike '万州'      then '万州区'
        |when chandi1 rlike '石景山'    then '石景山区'
        |when chandi1 rlike '河北'      then '河北区'
        |when chandi1 rlike '石柱土家' then '石柱土家族自治县'
        |when chandi1 rlike '江北'      then '江北区'
        |when chandi1 rlike '开州'      then '开州区'
        |when chandi1 rlike '和平'      then '和平区'
        |when chandi1 rlike '奉节县'    then '奉节县'
        |when chandi1 rlike '迪庆藏'  then '迪庆藏族自治州'
        |when chandi1 rlike '梁平县'    then '梁平县'
        |when chandi1 rlike '朝阳'      then '朝阳区'
        |when chandi1 rlike '普陀'      then '普陀区'
        |when chandi1 rlike '长宁'      then '长宁区'
        |when chandi1 rlike '虹口'      then '虹口区'
        |when chandi1 rlike '闵行'      then '闵行区'
        |when chandi1 rlike '松江'      then '松江区'
        |when chandi1 rlike '崇明'      then '崇明区'
        |when chandi1 rlike '徐汇'      then '徐汇区'
        |when chandi1 rlike '浦东新'    then '浦东新区'
        |when chandi1 rlike '黄浦'      then '黄浦区'
        |when chandi1 rlike '奉贤'      then '奉贤区'
        |when chandi1 rlike '宝山'      then '宝山区'
        |when chandi1 rlike '金山'      then '金山区'
        |when chandi1 rlike '嘉定'      then '嘉定区'
        |when chandi1 rlike '杨浦'      then '杨浦区'
        |when chandi1 rlike '青浦'      then '青浦区'
        |when chandi1 rlike '沈抚新'    then '沈抚新区'
        |else '-1' end as chandi_city,
        |
        |--品类
        |case when platformId in (9,10) then
        |(case
        |when  danpin_firstCate in ('苹果','橙子/橘子') then json_parse(base_info,'水果种类')
        |when  danpin_firstCate in ('车厘子/樱桃') then json_parse(base_info,'食品品种')
        | when  danpin_firstCate= '小龙虾' then  json_parse(base_info,'食品工艺')
        | when  danpin_firstCate= '蟹类' then  json_parse(base_info,'特产品类')
        | when  danpin_firstCate in ('红茶','绿茶','黑茶') then  json_parse(base_info,'茶种类')
        | else '-1' end)
        |
        |when platformId not in (9,10,5,3,20) then
        |(case
        |when  danpin_firstCate in ('苹果','橙子/橘子','车厘子/樱桃','大米','杂粮','小龙虾','蟹类','蜜饯干果') then json_parse(base_info,'产品名称')
        |when  danpin_firstCate in ('红茶','绿茶','黑茶') then json_parse(base_info,'品名')
        | else '-1' end)
        | else '-1' end as base_secondcate
        |from
        |--品类清洗完成
        |(select
        |*,
        |case when platformId in (9,10) then
        |(case
        |when categoryId='50050732' then '苹果'
        |when categoryId in ('50280002','123220003') then '橙子/橘子'
        |when categoryId in ('50050730') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('50016794','50050706','50050707','123220002') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('50016792',
        |'50016800',
        |'50050708',
        |'50050710',
        |'123188006',
        |'123200006',
        |'123224004',
        |'127692021',
        |'50050709',
        |'123222005')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') or categoryId in ('50598001') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when platformId in (5) then
        |(case
        |when categoryId='13554' then '苹果'
        |when categoryId in ('13555','13567') then '橙子/橘子'
        |when categoryId in ('13557') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('12242') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('12243')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') or categoryId in ('12204') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId in (3) then
        |(case
        |when categoryId='503926' then '苹果'
        |when categoryId='503923' and title rlike '橘' then '橙子/橘子'
        |when detailCategoryName in ('车厘子') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('503931') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('503934')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') or categoryId in ('500379') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId in (20) then
        |(case
        |when categoryId in ('8269','2447','171','7718') then '苹果'
        |when categoryId in ('5122','橙','1802','5123','桔子','165') then '橙子/橘子'
        |when categoryId in ('1212','车厘子樱桃','161','8249') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('虾类','175','8195') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('蟹类','176','8196')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when categoryId in ('6610','6611','6612','5272')  then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId not in (9,10,5,3,20) then
        |(case
        |when tbCategoryId in ('126198303','126198306','126198308') then '苹果'
        |when (categoryId in ('txd_10000040') and title rlike '橙') or (categoryId in ('txd_10000040') and title rlike '橘')
        | or tbCategoryId in ('127532102') or tbCategoryId in ('127532101') then '橙子/橘子'
        |when tbCategoryId in ('126198318') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when tbCategoryId in ('126198437',
        |'txd_10012075',
        |'127530162',
        |'127530159',
        |'127530160',
        |'127530161',
        |'127526171') and title rlike '小龙虾' then '小龙虾'
        |when tbCategoryId in ('127532156',
        |'127530102',
        |'127528157',
        |'127526172',
        |'126198448',
        |'126198436')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when tbCategoryId in ('126198766') and (title rlike '黑茶'
        |or title rlike '茯茶'
        |or title rlike '千两茶'
        |or title rlike '黑砖茶'
        |or title rlike '三尖'
        |or title rlike '青砖茶'
        |or title rlike '藏茶'
        |or title rlike '边茶'
        |or title rlike '安茶'
        |or title rlike '普洱熟茶'
        |or title rlike '六堡茶' ) then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |else '-1' end as danpin_firstCate,
        |--产地
        |case
        |when platformid in (9,10) then json_parse(base_info,'省份')
        |when platformid in (5,3,20) then '-1'
        |else json_parse(base_info,'产地') end as chandi,
        |case when platformid in (9,10) then json_parse(base_info,'城市')
        |when platformid in (5,3,20) then '-1'
        |else json_parse(base_info,'产地') end as chandi1
        |from
        |o2o_ddp_ODS))
        |
          """.stripMargin).where("danpin_firstCate!='-1'").dropDuplicates("timeStamp","platformId","good_id")

    result.repartition(5).write.orc(s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/result0407_puer/${year}_all")
  }
  def hmxs(sqlContext:SQLContext,year:String):Unit = {
    //清洗
    val sourcedata =
      sqlContext.read.orc(s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403_puer/${year}/hmxs/*/*")
    //o2o_ddp_ODS
    sourcedata.registerTempTable("o2o_ddp_ODS")
    val result = sqlContext.sql(
      """
        |select
        |*,
        |case
        |when danpin_firstCate = '苹果' then
        |(case
        |when base_secondcate like '%加力%'   then             '加力'
        |when base_secondcate like '%爱妃%'   then             '爱妃'
        |when base_secondcate like '%青苹果%'   then           '青苹果'
        |when base_secondcate like '%蛇果%'   then             '蛇果'
        |when base_secondcate like '%冰糖心%'   then           '冰糖心'
        |when base_secondcate like '%红富士%'   then           '红富士'
        |when base_secondcate like '%花牛%'   then             '花牛'
        |when base_secondcate like '%黄元帅%'   then           '黄元帅'
        |when base_secondcate like '%黄香蕉%'   then           '黄香蕉'
        |when base_secondcate like '%嘎拉%'   then             '嘎拉'
        |when base_secondcate like '%金冠%'   then             '金冠'
        |when base_secondcate like '%红星%'   then             '红星'
        |when base_secondcate like '%乔纳金%'   then           '乔纳金'
        |when base_secondcate like '%澳洲青果%'   then         '澳洲青果'
        |when base_secondcate like '%国光%'   then             '国光'
        |else (
        |case
        |when title like '%加力%'   then             '加力'
        |when title like '%爱妃%'   then             '爱妃'
        |when title like '%青苹果%'   then           '青苹果'
        |when title like '%蛇果%'   then             '蛇果'
        |when title like '%冰糖心%'   then           '冰糖心'
        |when title like '%红富士%'   then           '红富士'
        |when title like '%花牛%'   then             '花牛'
        |when title like '%黄元帅%'   then           '黄元帅'
        |when title like '%黄香蕉%'   then           '黄香蕉'
        |when title like '%嘎拉%'   then             '嘎拉'
        |when title like '%金冠%'   then             '金冠'
        |when title like '%红星%'   then             '红星'
        |when title like '%乔纳金%'   then           '乔纳金'
        |when title like '%澳洲青果%'   then         '澳洲青果'
        |when title like '%国光%'   then             '国光'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '橙子/橘子' then
        |(case
        |when base_secondcate like      '%南非橙%'          then      '南非橙'
        |when base_secondcate like      '%西班牙橙%'          then    '西班牙橙'
        |when base_secondcate like      '%绿橙%'            then     '绿橙'
        |when base_secondcate like      '%澳洲橙%'          then      '澳洲橙'
        |when base_secondcate like      '%新奇士%'            then    '新奇士'
        |when base_secondcate like      '%埃及橙%'            then    '埃及橙'
        |when base_secondcate like      '%夏橙%'          then       '夏橙'
        |when base_secondcate like      '%伦晚橙%'            then    '伦晚橙'
        |when base_secondcate like      '%茂古柑%'            then    '茂古柑'
        |when base_secondcate like      '%柳橙%'          then       '柳橙'
        |when base_secondcate like      '%皇帝柑%'          then      '皇帝柑'
        |when base_secondcate like      '%青见%'          then       '青见'
        |when base_secondcate like      '%贡柑%'            then     '贡柑'
        |when base_secondcate like      '%桔子%'             then    '桔子'
        |when base_secondcate like      '%芦柑%'          then       '芦柑'
        |when base_secondcate like      '%茂谷柑%'          then      '茂谷柑'
        |when base_secondcate like      '%金桔%'          then       '金桔'
        |when base_secondcate like      '%春见%'          then       '春见'
        |when base_secondcate like      '%丑柑%'          then       '丑柑'
        |when base_secondcate like      '%椪柑%'          then       '椪柑'
        |when base_secondcate like      '%蜜桔%'          then       '蜜桔'
        |when base_secondcate like      '%砂糖橘%'          then      '砂糖橘'
        |when base_secondcate like      '%不知火%'          then      '不知火'
        |else (
        |case
        |when title like      '%南非橙%'          then      '南非橙'
        |when title like      '%西班牙橙%'          then    '西班牙橙'
        |when title like      '%绿橙%'            then     '绿橙'
        |when title like      '%澳洲橙%'          then      '澳洲橙'
        |when title like      '%新奇士%'            then    '新奇士'
        |when title like      '%埃及橙%'            then    '埃及橙'
        |when title like      '%夏橙%'          then       '夏橙'
        |when title like      '%伦晚橙%'            then    '伦晚橙'
        |when title like      '%茂古柑%'            then    '茂古柑'
        |when title like      '%柳橙%'          then       '柳橙'
        |when title like      '%皇帝柑%'          then      '皇帝柑'
        |when title like      '%青见%'          then       '青见'
        |when title like      '%贡柑%'            then     '贡柑'
        |when title like      '%桔子%'             then    '桔子'
        |when title like      '%芦柑%'          then       '芦柑'
        |when title like      '%茂谷柑%'          then      '茂谷柑'
        |when title like      '%金桔%'          then       '金桔'
        |when title like      '%春见%'          then       '春见'
        |when title like      '%丑柑%'          then       '丑柑'
        |when title like      '%椪柑%'          then       '椪柑'
        |when title like      '%蜜桔%'          then       '蜜桔'
        |when title like      '%砂糖橘%'          then      '砂糖橘'
        |when title like      '%不知火%'          then      '不知火'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '车厘子/樱桃' then
        |(case
        |when base_secondcate like     '%美早%'          then    '美早'
        |when base_secondcate like     '%黑珍珠%'         then   '黑珍珠'
        |when base_secondcate like     '%大红灯%'        then    '大红灯'
        |when base_secondcate like     '%宾莹%'       then       '宾莹'
        |when base_secondcate like     '%水晶%'         then     '水晶'
        |when base_secondcate like     '%先锋%'         then     '先锋'
        |when base_secondcate like     '%拉宾斯%'      then      '拉宾斯'
        |when base_secondcate like     '%雷尼尔%'         then   '雷尼尔'
        |when base_secondcate like     '%萨米脱%'         then   '萨米脱'
        |when base_secondcate like     '%科迪亚%'      then      '科迪亚'
        |when base_secondcate like     '%意大利早红%'       then '意大利早红'
        |else (
        |case
        |when title like     '%美早%'          then    '美早'
        |when title like     '%黑珍珠%'         then   '黑珍珠'
        |when title like     '%大红灯%'        then    '大红灯'
        |when title like     '%宾莹%'       then       '宾莹'
        |when title like     '%水晶%'         then     '水晶'
        |when title like     '%先锋%'         then     '先锋'
        |when title like     '%拉宾斯%'      then      '拉宾斯'
        |when title like     '%雷尼尔%'         then   '雷尼尔'
        |when title like     '%萨米脱%'         then   '萨米脱'
        |when title like     '%科迪亚%'      then      '科迪亚'
        |when title like     '%意大利早红%'       then '意大利早红'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '小龙虾' then
        |(case
        |when base_secondcate like    '%虾类制品%'     then '虾类制品'
        |when base_secondcate like    '%活虾%'      then   '活虾'
        |when base_secondcate like    '%虾仁%'     then    '虾仁'
        |when base_secondcate like    '%虾米%'  then       '虾米'
        |when base_secondcate like    '%冻虾%'    then     '冻虾'
        |else (
        |case
        |when title like    '%虾类制品%'     then '虾类制品'
        |when title like    '%活虾%'      then   '活虾'
        |when title like    '%虾仁%'     then    '虾仁'
        |when title like    '%虾米%'  then       '虾米'
        |when title like    '%冻虾%'    then     '冻虾'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '蟹类' then
        |(case
        |when base_secondcate like    '%鲜活%'     then '鲜活'
        |when base_secondcate like    '%冷冻%'      then   '冷冻'
        |when base_secondcate like    '%冷藏%'     then    '冷藏'
        |else (
        |case
        |when title like    '%鲜活%'     then '鲜活'
        |when title like    '%冷冻%'      then   '冷冻'
        |when title like    '%冷藏%'     then    '冷藏'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('大米','杂粮') then
        |(case
        |when base_secondcate like   '%杂粮礼盒%'     then '杂粮礼盒'
        |when base_secondcate like   '%糙米%'      then   '糙米'
        |when base_secondcate like   '%小米%'     then    '小米'
        |when base_secondcate like   '%黑豆%'     then    '黑豆'
        |when base_secondcate like   '%黄豆%'      then   '黄豆'
        |when base_secondcate like   '%黑米%'     then    '黑米'
        |when base_secondcate like   '%薏米%'     then    '薏米'
        |when base_secondcate like   '%糯米%'      then   '糯米'
        |else (
        |case
        |when title like   '%杂粮礼盒%'     then '杂粮礼盒'
        |when title like   '%糙米%'      then   '糙米'
        |when title like   '%小米%'     then    '小米'
        |when title like   '%黑豆%'     then    '黑豆'
        |when title like   '%黄豆%'      then   '黄豆'
        |when title like   '%黑米%'     then    '黑米'
        |when title like   '%薏米%'     then    '薏米'
        |when title like   '%糯米%'      then   '糯米'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('红茶') then
        |(case
        |when base_secondcate like  '%正山小种%'     then     '正山小种'
        |when base_secondcate like  '%金骏眉%'      then     '金骏眉'
        |when base_secondcate like  '%滇红%'        then     '滇红'
        |when base_secondcate like  '%祁门%'       then      '祁门'
        |when base_secondcate like  '%再加工茶%'   then       '再加工茶'
        |else (
        |case
        |when title like  '%正山小种%'     then     '正山小种'
        |when title like  '%金骏眉%'      then     '金骏眉'
        |when title like  '%滇红%'        then     '滇红'
        |when title like  '%祁门%'       then      '祁门'
        |when title like  '%再加工茶%'   then       '再加工茶'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('绿茶') then
        |(case
        |when base_secondcate like  '%洞庭碧螺春%'    then     '洞庭碧螺春'
        |when base_secondcate like  '%西湖龙井%'    then      '西湖龙井'
        |when base_secondcate like  '%信阳毛尖%'    then      '信阳毛尖'
        |when base_secondcate like  '%日照绿茶%'   then       '日照绿茶'
        |when base_secondcate like  '%龙井%'  then            '龙井'
        |when base_secondcate like  '%竹叶青%'    then        '竹叶青'
        |when base_secondcate like  '%雀舌%'    then          '雀舌'
        |when base_secondcate like  '%黄山毛峰%'    then      '黄山毛峰'
        |when base_secondcate like  '%毛峰%'   then           '毛峰'
        |else (
        |case
        |when title like  '%洞庭碧螺春%'    then     '洞庭碧螺春'
        |when title like  '%西湖龙井%'    then      '西湖龙井'
        |when title like  '%信阳毛尖%'    then      '信阳毛尖'
        |when title like  '%日照绿茶%'   then       '日照绿茶'
        |when title like  '%龙井%'  then            '龙井'
        |when title like  '%竹叶青%'    then        '竹叶青'
        |when title like  '%雀舌%'    then          '雀舌'
        |when title like  '%黄山毛峰%'    then      '黄山毛峰'
        |when title like  '%毛峰%'   then           '毛峰'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('黑茶') then
        |(case
        |when base_secondcate like  '%黑茶%'      then     '黑茶'
        |when base_secondcate like  '%普洱%'     then    '普洱茶'
        |when base_secondcate like  '%老青茶%'     then    '老青茶'
        |when base_secondcate like  '%六堡茶%'    then     '六堡茶'
        |when base_secondcate like  '%藏茶%'   then        '藏茶'
        |else (
        |case
        |when title like  '%黑茶%'      then     '黑茶'
        |when title like  '%普洱%'     then    '普洱茶'
        |when title like  '%老青茶%'     then    '老青茶'
        |when title like  '%六堡茶%'    then     '六堡茶'
        |when title like  '%藏茶%'   then        '藏茶'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('蜜饯干果') then
        |(case
        |when base_secondcate like  '%梅类%'    then    '梅类'
        |when base_secondcate like  '%芒果干%'     then '芒果干'
        |when base_secondcate like  '%橄榄%'     then   '橄榄'
        |when base_secondcate like  '%葡萄干%'    then  '葡萄干'
        |when base_secondcate like  '%金桔%' then       '金桔'
        |when base_secondcate like  '%桃干%'    then    '桃干'
        |when base_secondcate like  '%草莓干%'     then '草莓干'
        |when base_secondcate like  '%无花果干%'     then '无花果干'
        |when base_secondcate like  '%李子%'    then    '李子'
        |when base_secondcate like  '%菠萝蜜%' then     '菠萝蜜'
        |else (
        |case
        |when title like  '%梅类%'    then    '梅类'
        |when title like  '%芒果干%'     then '芒果干'
        |when title like  '%橄榄%'     then   '橄榄'
        |when title like  '%葡萄干%'    then  '葡萄干'
        |when title like  '%金桔%' then       '金桔'
        |when title like  '%桃干%'    then    '桃干'
        |when title like  '%草莓干%'     then '草莓干'
        |when title like  '%无花果干%'     then '无花果干'
        |when title like  '%李子%'    then    '李子'
        |when title like  '%菠萝蜜%' then     '菠萝蜜'
        |else '其他' end
        |)end
        |)
        |
        |else '其他' end as danpin_secondCate
        |from
        |(select *,
        |case
        |when chandi like '湖南%'              then '湖南省'
        |when chandi like '云南%'              then '云南省'
        |when chandi like '河南%'              then '河南省'
        |when chandi like '江苏%'              then '江苏省'
        |when chandi like '甘肃%'              then '甘肃省'
        |when chandi like '黑龙江%'            then '黑龙江省'
        |when chandi like '天津%'              then '天津市'
        |when chandi like '广西壮族%'          then '广西壮族自治区'
        |when chandi like '陕西%'              then '陕西省'
        |when chandi like '宁夏回族%'          then '宁夏回族自治区'
        |when chandi like '湖北%'              then '湖北省'
        |when chandi like '重庆%'              then '重庆市'
        |when chandi like '江西%'              then '江西省'
        |when chandi like '四川%'              then '四川省'
        |when chandi like '山西%'              then '山西省'
        |when chandi like '青海%'              then '青海省'
        |when chandi like '福建%'              then '福建省'
        |when chandi like '河北%'              then '河北省'
        |when chandi like '海南%'              then '海南省'
        |when chandi like '新疆维吾尔%'        then '新疆维吾尔自治区'
        |when chandi like '广东%'              then '广东省'
        |when chandi like '北京%'              then '北京市'
        |when chandi like '辽宁%'              then '辽宁省'
        |when chandi like '安徽%'              then '安徽省'
        |when chandi like '吉林%'              then '吉林省'
        |when chandi like '浙江%'              then '浙江省'
        |when chandi like '西藏%'              then '西藏自治区'
        |when chandi like '内蒙古%'            then '内蒙古自治区'
        |when chandi like '上海%'              then '上海市'
        |when chandi like '山东%'              then '山东省'
        |when chandi like '贵州%'              then '贵州省'
        |else '-1' end as chandi_province,
        |case
        |when chandi1 rlike '中卫'      then '中卫市'
        |when chandi1 rlike '兰州'      then '兰州市'
        |when chandi1 rlike '嘉峪关'    then '嘉峪关市'
        |when chandi1 rlike '克拉玛依'  then '克拉玛依市'
        |when chandi1 rlike '西安'      then '西安市'
        |when chandi1 rlike '酒泉'      then '酒泉市'
        |when chandi1 rlike '伊犁哈萨克'then '伊犁哈萨克自治州'
        |when chandi1 rlike '咸阳'      then '咸阳市'
        |when chandi1 rlike '安康'      then '安康市'
        |when chandi1 rlike '汉中'      then '汉中市'
        |when chandi1 rlike '延安'      then '延安市'
        |when chandi1 rlike '陇南'      then '陇南市'
        |when chandi1 rlike '玉树藏'  then '玉树藏族自治州'
        |when chandi1 rlike '金昌'      then '金昌市'
        |when chandi1 rlike '庆阳'      then '庆阳市'
        |when chandi1 rlike '张掖'      then '张掖市'
        |when chandi1 rlike '渭南'      then '渭南市'
        |when chandi1 rlike '海南藏'  then '海南藏族自治州'
        |when chandi1 rlike '西宁'      then '西宁市'
        |when chandi1 rlike '天水'      then '天水市'
        |when chandi1 rlike '吐鲁番'    then '吐鲁番市'
        |when chandi1 rlike '喀什地'    then '喀什地区'
        |when chandi1 rlike '乌鲁木齐'  then '乌鲁木齐市'
        |when chandi1 rlike '巴音郭楞蒙古' then '巴音郭楞蒙古自治州'
        |when chandi1 rlike '辽源'      then '辽源市'
        |when chandi1 rlike '锦州'      then '锦州市'
        |when chandi1 rlike '大兴安岭地'then '大兴安岭地区'
        |when chandi1 rlike '松原'      then '松原市'
        |when chandi1 rlike '长春'      then '长春市'
        |when chandi1 rlike '伊春'      then '伊春市'
        |when chandi1 rlike '朝阳'      then '朝阳市'
        |when chandi1 rlike '阜新'      then '阜新市'
        |when chandi1 rlike '丹东'      then '丹东市'
        |when chandi1 rlike '双鸭山'    then '双鸭山市'
        |when chandi1 rlike '黑河'      then '黑河市'
        |when chandi1 rlike '大连'      then '大连市'
        |when chandi1 rlike '哈尔滨'    then '哈尔滨市'
        |when chandi1 rlike '四平'      then '四平市'
        |when chandi1 rlike '佳木斯'    then '佳木斯市'
        |when chandi1 rlike '辽阳'      then '辽阳市'
        |when chandi1 rlike '鸡西'      then '鸡西市'
        |when chandi1 rlike '本溪'      then '本溪市'
        |when chandi1 rlike '白山'      then '白山市'
        |when chandi1 rlike '七台河'    then '七台河市'
        |when chandi1 rlike '绥化'      then '绥化市'
        |when chandi1 rlike '吉林'      then '吉林市'
        |when chandi1 rlike '通化'      then '通化市'
        |when chandi1 rlike '遂宁'      then '遂宁市'
        |when chandi1 rlike '长治'      then '长治市'
        |when chandi1 rlike '达州'      then '达州市'
        |when chandi1 rlike '廊坊'      then '廊坊市'
        |when chandi1 rlike '六盘水'    then '六盘水市'
        |when chandi1 rlike '阳泉'      then '阳泉市'
        |when chandi1 rlike '毕节'      then '毕节市'
        |when chandi1 rlike '成都'      then '成都市'
        |when chandi1 rlike '锡林郭勒盟' then '锡林郭勒盟'
        |when chandi1 rlike '衡水'      then '衡水市'
        |when chandi1 rlike '攀枝花'    then '攀枝花市'
        |when chandi1 rlike '凉山彝'  then '凉山彝族自治州'
        |when chandi1 rlike '绵阳'      then '绵阳市'
        |when chandi1 rlike '昆明'      then '昆明市'
        |when chandi1 rlike '雅安'      then '雅安市'
        |when chandi1 rlike '广安'      then '广安市'
        |when chandi1 rlike '邯郸'      then '邯郸市'
        |when chandi1 rlike '天津'      then '天津市'
        |when chandi1 rlike '吕梁'      then '吕梁市'
        |when chandi1 rlike '宜宾'      then '宜宾市'
        |when chandi1 rlike '大同'      then '大同市'
        |when chandi1 rlike '儋州'      then '儋州市'
        |when chandi1 rlike '清远'      then '清远市'
        |when chandi1 rlike '江门'      then '江门市'
        |when chandi1 rlike '河源'      then '河源市'
        |when chandi1 rlike '三亚'      then '三亚市'
        |when chandi1 rlike '玉林'      then '玉林市'
        |when chandi1 rlike '中山'      then '中山市'
        |when chandi1 rlike '贺州'      then '贺州市'
        |when chandi1 rlike '来宾'      then '来宾市'
        |when chandi1 rlike '柳州'      then '柳州市'
        |when chandi1 rlike '佛山'      then '佛山市'
        |when chandi1 rlike '珠海'      then '珠海市'
        |when chandi1 rlike '汕尾'      then '汕尾市'
        |when chandi1 rlike '韶关'      then '韶关市'
        |when chandi1 rlike '东莞'      then '东莞市'
        |when chandi1 rlike '肇庆'      then '肇庆市'
        |when chandi1 rlike '惠州'      then '惠州市'
        |when chandi1 rlike '汕头'      then '汕头市'
        |when chandi1 rlike '河池'      then '河池市'
        |when chandi1 rlike '百色'      then '百色市'
        |when chandi1 rlike '黄冈'      then '黄冈市'
        |when chandi1 rlike '娄底'      then '娄底市'
        |when chandi1 rlike '湘潭'      then '湘潭市'
        |when chandi1 rlike '岳阳'      then '岳阳市'
        |when chandi1 rlike '常德'      then '常德市'
        |when chandi1 rlike '黄石'      then '黄石市'
        |when chandi1 rlike '荆门'      then '荆门市'
        |when chandi1 rlike '武汉'      then '武汉市'
        |when chandi1 rlike '宜昌'      then '宜昌市'
        |when chandi1 rlike '张家界'    then '张家界市'
        |when chandi1 rlike '咸宁'      then '咸宁市'
        |when chandi1 rlike '驻马店'    then '驻马店市'
        |when chandi1 rlike '随州'      then '随州市'
        |when chandi1 rlike '商丘'      then '商丘市'
        |when chandi1 rlike '洛阳'      then '洛阳市'
        |when chandi1 rlike '永州'      then '永州市'
        |when chandi1 rlike '安阳'      then '安阳市'
        |when chandi1 rlike '鹤壁'      then '鹤壁市'
        |when chandi1 rlike '益阳'      then '益阳市'
        |when chandi1 rlike '平顶山'    then '平顶山市'
        |when chandi1 rlike '荆州'      then '荆州市'
        |when chandi1 rlike '株洲'      then '株洲市'
        |when chandi1 rlike '信阳'      then '信阳市'
        |when chandi1 rlike '南阳'      then '南阳市'
        |when chandi1 rlike '濮阳'      then '濮阳市'
        |when chandi1 rlike '开封'      then '开封市'
        |when chandi1 rlike '白银'      then '白银市'
        |when chandi1 rlike '平凉'      then '平凉市'
        |when chandi1 rlike '铜川'      then '铜川市'
        |when chandi1 rlike '阿克苏地'  then '阿克苏地区'
        |when chandi1 rlike '哈密'      then '哈密市'
        |when chandi1 rlike '商洛'      then '商洛市'
        |when chandi1 rlike '宝鸡'      then '宝鸡市'
        |when chandi1 rlike '石嘴山'    then '石嘴山市'
        |when chandi1 rlike '甘南藏'  then '甘南藏族自治州'
        |when chandi1 rlike '武威'      then '武威市'
        |when chandi1 rlike '沈阳'      then '沈阳市'
        |when chandi1 rlike '盘锦'      then '盘锦市'
        |when chandi1 rlike '铁岭'      then '铁岭市'
        |when chandi1 rlike '齐齐哈尔'  then '齐齐哈尔市'
        |when chandi1 rlike '牡丹江'    then '牡丹江市'
        |when chandi1 rlike '营口'      then '营口市'
        |when chandi1 rlike '大庆'      then '大庆市'
        |when chandi1 rlike '抚顺'      then '抚顺市'
        |when chandi1 rlike '运城'      then '运城市'
        |when chandi1 rlike '沧州'      then '沧州市'
        |when chandi1 rlike '黔南布依族苗' then '黔南布依族苗族自治州'
        |when chandi1 rlike '红河哈尼族彝' then '红河哈尼族彝族自治州'
        |when chandi1 rlike '楚雄彝'  then '楚雄彝族自治州'
        |when chandi1 rlike '门头沟'    then '门头沟区'
        |when chandi1 rlike '呼和浩特'  then '呼和浩特市'
        |when chandi1 rlike '保定'      then '保定市'
        |when chandi1 rlike '眉山'      then '眉山市'
        |when chandi1 rlike '重庆'      then '重庆市'
        |when chandi1 rlike '铜仁'      then '铜仁市'
        |when chandi1 rlike '自贡'      then '自贡市'
        |when chandi1 rlike '巴中'      then '巴中市'
        |when chandi1 rlike '广元'      then '广元市'
        |when chandi1 rlike '晋城'      then '晋城市'
        |when chandi1 rlike '揭阳'      then '揭阳市'
        |when chandi1 rlike '钦州'      then '钦州市'
        |when chandi1 rlike '海口'      then '海口市'
        |when chandi1 rlike '湛江'      then '湛江市'
        |when chandi1 rlike '深圳'      then '深圳市'
        |when chandi1 rlike '潮州'      then '潮州市'
        |when chandi1 rlike '三沙'      then '三沙市'
        |when chandi1 rlike '云浮'      then '云浮市'
        |when chandi1 rlike '茂名'      then '茂名市'
        |when chandi1 rlike '周口'      then '周口市'
        |when chandi1 rlike '郑州'      then '郑州市'
        |when chandi1 rlike '许昌'      then '许昌市'
        |when chandi1 rlike '三门峡'    then '三门峡市'
        |when chandi1 rlike '怀化'      then '怀化市'
        |when chandi1 rlike '襄阳'      then '襄阳市'
        |when chandi1 rlike '衡阳'      then '衡阳市'
        |when chandi1 rlike '漯河'      then '漯河市'
        |when chandi1 rlike '十堰'      then '十堰市'
        |when chandi1 rlike '郴州'      then '郴州市'
        |when chandi1 rlike '马鞍山'    then '马鞍山市'
        |when chandi1 rlike '烟台'      then '烟台市'
        |when chandi1 rlike '九江'      then '九江市'
        |when chandi1 rlike '龙岩'      then '龙岩市'
        |when chandi1 rlike '聊城'      then '聊城市'
        |when chandi1 rlike '济宁'      then '济宁市'
        |when chandi1 rlike '常州'      then '常州市'
        |when chandi1 rlike '宁德'      then '宁德市'
        |when chandi1 rlike '德州'      then '德州市'
        |when chandi1 rlike '临沂'      then '临沂市'
        |when chandi1 rlike '青岛'      then '青岛市'
        |when chandi1 rlike '新余'      then '新余市'
        |when chandi1 rlike '厦门'      then '厦门市'
        |when chandi1 rlike '滁州'      then '滁州市'
        |when chandi1 rlike '苏州'      then '苏州市'
        |when chandi1 rlike '南昌'      then '南昌市'
        |when chandi1 rlike '滨州'      then '滨州市'
        |when chandi1 rlike '南平'      then '南平市'
        |when chandi1 rlike '宁波'      then '宁波市'
        |when chandi1 rlike '淄博'      then '淄博市'
        |when chandi1 rlike '菏泽'      then '菏泽市'
        |when chandi1 rlike '徐州'      then '徐州市'
        |when chandi1 rlike '景德镇'    then '景德镇市'
        |when chandi1 rlike '莆田'      then '莆田市'
        |when chandi1 rlike '台州'      then '台州市'
        |when chandi1 rlike '福州'      then '福州市'
        |when chandi1 rlike '漳州'      then '漳州市'
        |when chandi1 rlike '上饶'      then '上饶市'
        |when chandi1 rlike '济南'      then '济南市'
        |when chandi1 rlike '泉州'      then '泉州市'
        |when chandi1 rlike '嘉兴'      then '嘉兴市'
        |when chandi1 rlike '连云港'    then '连云港市'
        |when chandi1 rlike '金华'      then '金华市'
        |when chandi1 rlike '淮北'      then '淮北市'
        |when chandi1 rlike '上海'      then '上海市'
        |when chandi1 rlike '威海'      then '威海市'
        |when chandi1 rlike '东营'      then '东营市'
        |when chandi1 rlike '蚌埠'      then '蚌埠市'
        |when chandi1 rlike '日照'      then '日照市'
        |when chandi1 rlike '淮安'      then '淮安市'
        |when chandi1 rlike '黄山'      then '黄山市'
        |when chandi1 rlike '焦作'      then '焦作市'
        |when chandi1 rlike '恩施土家族苗' then '恩施土家族苗族自治州'
        |when chandi1 rlike '邵阳'      then '邵阳市'
        |when chandi1 rlike '海东'      then '海东市'
        |when chandi1 rlike '定西'      then '定西市'
        |when chandi1 rlike '黄南藏'  then '黄南藏族自治州'
        |when chandi1 rlike '固原'      then '固原市'
        |when chandi1 rlike '新乡'      then '新乡市'
        |when chandi1 rlike '鄂州'      then '鄂州市'
        |when chandi1 rlike '榆林'      then '榆林市'
        |when chandi1 rlike '银川'      then '银川市'
        |when chandi1 rlike '海西蒙古族藏' then '海西蒙古族藏族自治州'
        |when chandi1 rlike '塔城地'    then '塔城地区'
        |when chandi1 rlike '鞍山'      then '鞍山市'
        |when chandi1 rlike '延边朝鲜' then '延边朝鲜族自治州'
        |when chandi1 rlike '阿勒泰地'  then '阿勒泰地区'
        |when chandi1 rlike '博尔塔拉蒙古' then '博尔塔拉蒙古自治州'
        |when chandi1 rlike '吴忠'      then '吴忠市'
        |when chandi1 rlike '果洛藏'  then '果洛藏族自治州'
        |when chandi1 rlike '内江'      then '内江市'
        |when chandi1 rlike '泸州'      then '泸州市'
        |when chandi1 rlike '朔州'      then '朔州市'
        |when chandi1 rlike '张家口'    then '张家口市'
        |when chandi1 rlike '玉溪'      then '玉溪市'
        |when chandi1 rlike '北京'      then '北京市'
        |when chandi1 rlike '邢台'      then '邢台市'
        |when chandi1 rlike '兴安盟'    then '兴安盟'
        |when chandi1 rlike '临汾'      then '临汾市'
        |when chandi1 rlike '丽江'      then '丽江市'
        |when chandi1 rlike '梅州'      then '梅州市'
        |when chandi1 rlike '贵港'      then '贵港市'
        |when chandi1 rlike '崇左'      then '崇左市'
        |when chandi1 rlike '阳江'      then '阳江市'
        |when chandi1 rlike '广州'      then '广州市'
        |when chandi1 rlike '南宁'      then '南宁市'
        |when chandi1 rlike '孝感'      then '孝感市'
        |when chandi1 rlike '长沙'      then '长沙市'
        |when chandi1 rlike '昌吉回'  then '昌吉回族自治州'
        |when chandi1 rlike '北海'      then '北海市'
        |when chandi1 rlike '桂林'      then '桂林市'
        |when chandi1 rlike '梧州'      then '梧州市'
        |when chandi1 rlike '葫芦岛'    then '葫芦岛市'
        |when chandi1 rlike '唐山'      then '唐山市'
        |when chandi1 rlike '资阳'      then '资阳市'
        |when chandi1 rlike '那曲'      then '那曲市'
        |when chandi1 rlike '秦皇岛'    then '秦皇岛市'
        |when chandi1 rlike '鄂尔多斯'  then '鄂尔多斯市'
        |when chandi1 rlike '承德'      then '承德市'
        |when chandi1 rlike '昭通'      then '昭通市'
        |when chandi1 rlike '忻州'      then '忻州市'
        |when chandi1 rlike '安顺'      then '安顺市'
        |when chandi1 rlike '湘西土家族苗' then '湘西土家族苗族自治州'
        |when chandi1 rlike '白城'      then '白城市'
        |when chandi1 rlike '保山'      then '保山市'
        |when chandi1 rlike '乐山'      then '乐山市'
        |when chandi1 rlike '临沧'      then '临沧市'
        |when chandi1 rlike '南充'      then '南充市'
        |when chandi1 rlike '甘孜藏'  then '甘孜藏族自治州'
        |when chandi1 rlike '和田地'    then '和田地区'
        |when chandi1 rlike '太原'      then '太原市'
        |when chandi1 rlike '静海'      then '静海区'
        |when chandi1 rlike '呼伦贝尔'  then '呼伦贝尔市'
        |when chandi1 rlike '山南'      then '山南市'
        |when chandi1 rlike '合川'      then '合川区'
        |when chandi1 rlike '黔东南苗族侗' then '黔东南苗族侗族自治州'
        |when chandi1 rlike '赤峰'      then '赤峰市'
        |when chandi1 rlike '贵阳'      then '贵阳市'
        |when chandi1 rlike '防城港'    then '防城港市'
        |when chandi1 rlike '鹤岗'      then '鹤岗市'
        |when chandi1 rlike '石家庄'    then '石家庄市'
        |when chandi1 rlike '德阳'      then '德阳市'
        |when chandi1 rlike '遵义'      then '遵义市'
        |when chandi1 rlike '海北藏'  then '海北藏族自治州'
        |when chandi1 rlike '晋中'      then '晋中市'
        |when chandi1 rlike '阿拉善盟'  then '阿拉善盟'
        |when chandi1 rlike '涪陵'      then '涪陵区'
        |when chandi1 rlike '日喀则'    then '日喀则市'
        |when chandi1 rlike '酉阳土家族苗' then '酉阳土家族苗族自治县'
        |when chandi1 rlike '怒江傈僳' then '怒江傈僳族自治州'
        |when chandi1 rlike '乌海'      then '乌海市'
        |when chandi1 rlike '巴彦淖尔'  then '巴彦淖尔市'
        |when chandi1 rlike '南京'      then '南京市'
        |when chandi1 rlike '三明'      then '三明市'
        |when chandi1 rlike '宣城'      then '宣城市'
        |when chandi1 rlike '湖州'      then '湖州市'
        |when chandi1 rlike '枣庄'      then '枣庄市'
        |when chandi1 rlike '抚州'      then '抚州市'
        |when chandi1 rlike '宿州'      then '宿州市'
        |when chandi1 rlike '安庆'      then '安庆市'
        |when chandi1 rlike '衢州'      then '衢州市'
        |when chandi1 rlike '西双版纳傣' then '西双版纳傣族自治州'
        |when chandi1 rlike '绍兴'      then '绍兴市'
        |when chandi1 rlike '萍乡'      then '萍乡市'
        |when chandi1 rlike '吉安'      then '吉安市'
        |when chandi1 rlike '泰安'      then '泰安市'
        |when chandi1 rlike '丽水'      then '丽水市'
        |when chandi1 rlike '宿迁'      then '宿迁市'
        |when chandi1 rlike '临夏回'  then '临夏回族自治州'
        |when chandi1 rlike '北辰'      then '北辰区'
        |when chandi1 rlike '武隆县'    then '武隆县'
        |when chandi1 rlike '拉萨'      then '拉萨市'
        |when chandi1 rlike '南川'      then '南川区'
        |when chandi1 rlike '红桥'      then '红桥区'
        |when chandi1 rlike '扬州'      then '扬州市'
        |when chandi1 rlike '温州'      then '温州市'
        |when chandi1 rlike '阜阳'      then '阜阳市'
        |when chandi1 rlike '亳州'      then '亳州市'
        |when chandi1 rlike '包头'      then '包头市'
        |when chandi1 rlike '巫溪县'    then '巫溪县'
        |when chandi1 rlike '普洱'      then '普洱市'
        |when chandi1 rlike '那曲地'    then '那曲地区'
        |when chandi1 rlike '盐城'      then '盐城市'
        |when chandi1 rlike '合肥'      then '合肥市'
        |when chandi1 rlike '泰州'      then '泰州市'
        |when chandi1 rlike '淮南'      then '淮南市'
        |when chandi1 rlike '曲靖'      then '曲靖市'
        |when chandi1 rlike '铜陵'      then '铜陵市'
        |when chandi1 rlike '赣州'      then '赣州市'
        |when chandi1 rlike '静安'      then '静安区'
        |when chandi1 rlike '通辽'      then '通辽市'
        |when chandi1 rlike '芜湖'      then '芜湖市'
        |when chandi1 rlike '杭州'      then '杭州市'
        |when chandi1 rlike '武清'      then '武清区'
        |when chandi1 rlike '南岸'      then '南岸区'
        |when chandi1 rlike '海淀'      then '海淀区'
        |when chandi1 rlike '九龙坡'    then '九龙坡区'
        |when chandi1 rlike '镇江'      then '镇江市'
        |when chandi1 rlike '潍坊'      then '潍坊市'
        |when chandi1 rlike '长寿'      then '长寿区'
        |when chandi1 rlike '无锡'      then '无锡市'
        |when chandi1 rlike '池州'      then '池州市'
        |when chandi1 rlike '鹰潭'      then '鹰潭市'
        |when chandi1 rlike '六安'      then '六安市'
        |when chandi1 rlike '宜春'      then '宜春市'
        |when chandi1 rlike '南通'      then '南通市'
        |when chandi1 rlike '荣昌'      then '荣昌区'
        |when chandi1 rlike '昌都'      then '昌都市'
        |when chandi1 rlike '大理白'  then '大理白族自治州'
        |when chandi1 rlike '河西'      then '河西区'
        |when chandi1 rlike '舟山'      then '舟山市'
        |when chandi1 rlike '克孜勒苏柯尔克孜' then '克孜勒苏柯尔克孜自治州'
        |when chandi1 rlike '秀山土家族苗'then '秀山土家族苗族自治县'
        |when chandi1 rlike '黔西南布依族苗' then '黔西南布依族苗族自治州'
        |when chandi1 rlike '璧山'      then '璧山区'
        |when chandi1 rlike '东丽'      then '东丽区'
        |when chandi1 rlike '密云'      then '密云区'
        |when chandi1 rlike '沙坪坝'    then '沙坪坝区'
        |when chandi1 rlike '北碚'      then '北碚区'
        |when chandi1 rlike '延庆'      then '延庆区'
        |when chandi1 rlike '乌兰察布'  then '乌兰察布市'
        |when chandi1 rlike '阿里地'    then '阿里地区'
        |when chandi1 rlike '房山'      then '房山区'
        |when chandi1 rlike '蓟州'      then '蓟州区'
        |when chandi1 rlike '昌平'      then '昌平区'
        |when chandi1 rlike '渝北'      then '渝北区'
        |when chandi1 rlike '巴南'      then '巴南区'
        |when chandi1 rlike '顺义'      then '顺义区'
        |when chandi1 rlike '城口县'    then '城口县'
        |when chandi1 rlike '潼南'      then '潼南区'
        |when chandi1 rlike '垫江县'    then '垫江县'
        |when chandi1 rlike '江津'      then '江津区'
        |when chandi1 rlike '怀柔'      then '怀柔区'
        |when chandi1 rlike '平谷'      then '平谷区'
        |when chandi1 rlike '忠县'      then '忠县'
        |when chandi1 rlike '西青'      then '西青区'
        |when chandi1 rlike '大足'      then '大足区'
        |when chandi1 rlike '林芝'      then '林芝市'
        |when chandi1 rlike '南开'      then '南开区'
        |when chandi1 rlike '通州'      then '通州区'
        |when chandi1 rlike '文山壮族苗' then '文山壮族苗族自治州'
        |when chandi1 rlike '滨海新'    then '滨海新区'
        |when chandi1 rlike '大兴'      then '大兴区'
        |when chandi1 rlike '德宏傣族景颇' then '德宏傣族景颇族自治州'
        |when chandi1 rlike '綦江'      then '綦江区'
        |when chandi1 rlike '丰台'      then '丰台区'
        |when chandi1 rlike '渝中'      then '渝中区'
        |when chandi1 rlike '黔江'      then '黔江区'
        |when chandi1 rlike '东城'      then '东城区'
        |when chandi1 rlike '云阳县'    then '云阳县'
        |when chandi1 rlike '彭水苗族土家'   then '彭水苗族土家族自治县'
        |when chandi1 rlike '巫山县'    then '巫山县'
        |when chandi1 rlike '河东'      then '河东区'
        |when chandi1 rlike '宝坻'      then '宝坻区'
        |when chandi1 rlike '铜梁'      then '铜梁区'
        |when chandi1 rlike '津南'      then '津南区'
        |when chandi1 rlike '丰都县'    then '丰都县'
        |when chandi1 rlike '阿坝藏族羌' then '阿坝藏族羌族自治州'
        |when chandi1 rlike '西城'      then '西城区'
        |when chandi1 rlike '宁河'      then '宁河区'
        |when chandi1 rlike '大渡口'    then '大渡口区'
        |when chandi1 rlike '永川'      then '永川区'
        |when chandi1 rlike '万州'      then '万州区'
        |when chandi1 rlike '石景山'    then '石景山区'
        |when chandi1 rlike '河北'      then '河北区'
        |when chandi1 rlike '石柱土家' then '石柱土家族自治县'
        |when chandi1 rlike '江北'      then '江北区'
        |when chandi1 rlike '开州'      then '开州区'
        |when chandi1 rlike '和平'      then '和平区'
        |when chandi1 rlike '奉节县'    then '奉节县'
        |when chandi1 rlike '迪庆藏'  then '迪庆藏族自治州'
        |when chandi1 rlike '梁平县'    then '梁平县'
        |when chandi1 rlike '朝阳'      then '朝阳区'
        |when chandi1 rlike '普陀'      then '普陀区'
        |when chandi1 rlike '长宁'      then '长宁区'
        |when chandi1 rlike '虹口'      then '虹口区'
        |when chandi1 rlike '闵行'      then '闵行区'
        |when chandi1 rlike '松江'      then '松江区'
        |when chandi1 rlike '崇明'      then '崇明区'
        |when chandi1 rlike '徐汇'      then '徐汇区'
        |when chandi1 rlike '浦东新'    then '浦东新区'
        |when chandi1 rlike '黄浦'      then '黄浦区'
        |when chandi1 rlike '奉贤'      then '奉贤区'
        |when chandi1 rlike '宝山'      then '宝山区'
        |when chandi1 rlike '金山'      then '金山区'
        |when chandi1 rlike '嘉定'      then '嘉定区'
        |when chandi1 rlike '杨浦'      then '杨浦区'
        |when chandi1 rlike '青浦'      then '青浦区'
        |when chandi1 rlike '沈抚新'    then '沈抚新区'
        |else '-1' end as chandi_city,
        |
        |--品类
        |case when platformId in (9,10) then
        |(case
        |when  danpin_firstCate in ('苹果','橙子/橘子') then json_parse(base_info,'水果种类')
        |when  danpin_firstCate in ('车厘子/樱桃') then json_parse(base_info,'食品品种')
        | when  danpin_firstCate= '小龙虾' then  json_parse(base_info,'食品工艺')
        | when  danpin_firstCate= '蟹类' then  json_parse(base_info,'特产品类')
        | when  danpin_firstCate in ('红茶','绿茶','黑茶') then  json_parse(base_info,'茶种类')
        | else '-1' end)
        |
        |when platformId not in (9,10,5,3,20) then
        |(case
        |when  danpin_firstCate in ('苹果','橙子/橘子','车厘子/樱桃','大米','杂粮','小龙虾','蟹类','蜜饯干果') then json_parse(base_info,'产品名称')
        |when  danpin_firstCate in ('红茶','绿茶','黑茶') then json_parse(base_info,'品名')
        | else '-1' end)
        | else '-1' end as base_secondcate
        |from
        |--品类清洗完成
        |(select
        |*,
        |case when platformId in (9,10) then
        |(case
        |when categoryId='50050732' then '苹果'
        |when categoryId in ('50280002','123220003') then '橙子/橘子'
        |when categoryId in ('50050730') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('50016794','50050706','50050707','123220002') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('50016792',
        |'50016800',
        |'50050708',
        |'50050710',
        |'123188006',
        |'123200006',
        |'123224004',
        |'127692021',
        |'50050709',
        |'123222005')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when platformId in (5) then
        |(case
        |when categoryId='13554' then '苹果'
        |when categoryId in ('13555','13567') then '橙子/橘子'
        |when categoryId in ('13557') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('12242') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('12243')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId in (3) then
        |(case
        |when categoryId='503926' then '苹果'
        |when categoryId='503923' and title rlike '橘' then '橙子/橘子'
        |when detailCategoryName in ('车厘子') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('503931') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('503934')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId in (20) then
        |(case
        |when categoryId in ('8269','2447','171','7718') then '苹果'
        |when categoryId in ('5122','橙','1802','5123','桔子','165') then '橙子/橘子'
        |when categoryId in ('1212','车厘子樱桃','161','8249') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('虾类','175','8195') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('蟹类','176','8196')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when categoryId in ('6610','6611','6612','5272') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId not in (9,10,5,3,20) then
        |(case
        |when tbCategoryId in ('126198303','126198306','126198308') then '苹果'
        |when (categoryId in ('txd_10000040') and title rlike '橙') or (categoryId in ('txd_10000040') and title rlike '橘')
        | or tbCategoryId in ('127532102') or tbCategoryId in ('127532101') then '橙子/橘子'
        |when tbCategoryId in ('126198318') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when tbCategoryId in ('126198437',
        |'txd_10012075',
        |'127530162',
        |'127530159',
        |'127530160',
        |'127530161',
        |'127526171') and title rlike '小龙虾' then '小龙虾'
        |when tbCategoryId in ('127532156',
        |'127530102',
        |'127528157',
        |'127526172',
        |'126198448',
        |'126198436')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when (tbCategoryId in ('126198766','126198762') and (title rlike '黑茶'
        |or title rlike '茯茶'
        |or title rlike '千两茶'
        |or title rlike '黑砖茶'
        |or title rlike '三尖'
        |or title rlike '青砖茶'
        |or title rlike '藏茶'
        |or title rlike '边茶'
        |or title rlike '安茶'
        |or title rlike '普洱熟茶'
        |or title rlike '六堡茶' ) ) or tbCategoryId in ('126198762') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |else '-1' end as danpin_firstCate,
        |--产地
        |case
        |when platformid in (9,10) then json_parse(base_info,'省份')
        |when platformid in (5,3,20) then '-1'
        |else json_parse(base_info,'产地') end as chandi,
        |case when platformid in (9,10) then json_parse(base_info,'城市')
        |when platformid in (5,3,20) then '-1'
        |else json_parse(base_info,'产地') end as chandi1
        |from
        |o2o_ddp_ODS))
        |
          """.stripMargin).where("danpin_firstCate!='-1'").dropDuplicates("timeStamp","platformId","good_id")

    result.repartition(5).write.orc(s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/result0407_puer/${year}_hmxs")
  }

  def pinduoduo(sqlContext:SQLContext,year:String):Unit = {
    //清洗
    val sourcedata =
      sqlContext.read.orc(
        s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/source_fourthCategoryId0403_puer/${year}/pinduoduo/*/*")
        .withColumn("tbCategoryId",lit("-1"))
    //o2o_ddp_ODS
    sourcedata.registerTempTable("o2o_ddp_ODS")
    val result = sqlContext.sql(
      """
        |select
        |*,
        |case
        |when danpin_firstCate = '苹果' then
        |(case
        |when base_secondcate like '%加力%'   then             '加力'
        |when base_secondcate like '%爱妃%'   then             '爱妃'
        |when base_secondcate like '%青苹果%'   then           '青苹果'
        |when base_secondcate like '%蛇果%'   then             '蛇果'
        |when base_secondcate like '%冰糖心%'   then           '冰糖心'
        |when base_secondcate like '%红富士%'   then           '红富士'
        |when base_secondcate like '%花牛%'   then             '花牛'
        |when base_secondcate like '%黄元帅%'   then           '黄元帅'
        |when base_secondcate like '%黄香蕉%'   then           '黄香蕉'
        |when base_secondcate like '%嘎拉%'   then             '嘎拉'
        |when base_secondcate like '%金冠%'   then             '金冠'
        |when base_secondcate like '%红星%'   then             '红星'
        |when base_secondcate like '%乔纳金%'   then           '乔纳金'
        |when base_secondcate like '%澳洲青果%'   then         '澳洲青果'
        |when base_secondcate like '%国光%'   then             '国光'
        |else (
        |case
        |when title like '%加力%'   then             '加力'
        |when title like '%爱妃%'   then             '爱妃'
        |when title like '%青苹果%'   then           '青苹果'
        |when title like '%蛇果%'   then             '蛇果'
        |when title like '%冰糖心%'   then           '冰糖心'
        |when title like '%红富士%'   then           '红富士'
        |when title like '%花牛%'   then             '花牛'
        |when title like '%黄元帅%'   then           '黄元帅'
        |when title like '%黄香蕉%'   then           '黄香蕉'
        |when title like '%嘎拉%'   then             '嘎拉'
        |when title like '%金冠%'   then             '金冠'
        |when title like '%红星%'   then             '红星'
        |when title like '%乔纳金%'   then           '乔纳金'
        |when title like '%澳洲青果%'   then         '澳洲青果'
        |when title like '%国光%'   then             '国光'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '橙子/橘子' then
        |(case
        |when base_secondcate like      '%南非橙%'          then      '南非橙'
        |when base_secondcate like      '%西班牙橙%'          then    '西班牙橙'
        |when base_secondcate like      '%绿橙%'            then     '绿橙'
        |when base_secondcate like      '%澳洲橙%'          then      '澳洲橙'
        |when base_secondcate like      '%新奇士%'            then    '新奇士'
        |when base_secondcate like      '%埃及橙%'            then    '埃及橙'
        |when base_secondcate like      '%夏橙%'          then       '夏橙'
        |when base_secondcate like      '%伦晚橙%'            then    '伦晚橙'
        |when base_secondcate like      '%茂古柑%'            then    '茂古柑'
        |when base_secondcate like      '%柳橙%'          then       '柳橙'
        |when base_secondcate like      '%皇帝柑%'          then      '皇帝柑'
        |when base_secondcate like      '%青见%'          then       '青见'
        |when base_secondcate like      '%贡柑%'            then     '贡柑'
        |when base_secondcate like      '%桔子%'             then    '桔子'
        |when base_secondcate like      '%芦柑%'          then       '芦柑'
        |when base_secondcate like      '%茂谷柑%'          then      '茂谷柑'
        |when base_secondcate like      '%金桔%'          then       '金桔'
        |when base_secondcate like      '%春见%'          then       '春见'
        |when base_secondcate like      '%丑柑%'          then       '丑柑'
        |when base_secondcate like      '%椪柑%'          then       '椪柑'
        |when base_secondcate like      '%蜜桔%'          then       '蜜桔'
        |when base_secondcate like      '%砂糖橘%'          then      '砂糖橘'
        |when base_secondcate like      '%不知火%'          then      '不知火'
        |else (
        |case
        |when title like      '%南非橙%'          then      '南非橙'
        |when title like      '%西班牙橙%'          then    '西班牙橙'
        |when title like      '%绿橙%'            then     '绿橙'
        |when title like      '%澳洲橙%'          then      '澳洲橙'
        |when title like      '%新奇士%'            then    '新奇士'
        |when title like      '%埃及橙%'            then    '埃及橙'
        |when title like      '%夏橙%'          then       '夏橙'
        |when title like      '%伦晚橙%'            then    '伦晚橙'
        |when title like      '%茂古柑%'            then    '茂古柑'
        |when title like      '%柳橙%'          then       '柳橙'
        |when title like      '%皇帝柑%'          then      '皇帝柑'
        |when title like      '%青见%'          then       '青见'
        |when title like      '%贡柑%'            then     '贡柑'
        |when title like      '%桔子%'             then    '桔子'
        |when title like      '%芦柑%'          then       '芦柑'
        |when title like      '%茂谷柑%'          then      '茂谷柑'
        |when title like      '%金桔%'          then       '金桔'
        |when title like      '%春见%'          then       '春见'
        |when title like      '%丑柑%'          then       '丑柑'
        |when title like      '%椪柑%'          then       '椪柑'
        |when title like      '%蜜桔%'          then       '蜜桔'
        |when title like      '%砂糖橘%'          then      '砂糖橘'
        |when title like      '%不知火%'          then      '不知火'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '车厘子/樱桃' then
        |(case
        |when base_secondcate like     '%美早%'          then    '美早'
        |when base_secondcate like     '%黑珍珠%'         then   '黑珍珠'
        |when base_secondcate like     '%大红灯%'        then    '大红灯'
        |when base_secondcate like     '%宾莹%'       then       '宾莹'
        |when base_secondcate like     '%水晶%'         then     '水晶'
        |when base_secondcate like     '%先锋%'         then     '先锋'
        |when base_secondcate like     '%拉宾斯%'      then      '拉宾斯'
        |when base_secondcate like     '%雷尼尔%'         then   '雷尼尔'
        |when base_secondcate like     '%萨米脱%'         then   '萨米脱'
        |when base_secondcate like     '%科迪亚%'      then      '科迪亚'
        |when base_secondcate like     '%意大利早红%'       then '意大利早红'
        |else (
        |case
        |when title like     '%美早%'          then    '美早'
        |when title like     '%黑珍珠%'         then   '黑珍珠'
        |when title like     '%大红灯%'        then    '大红灯'
        |when title like     '%宾莹%'       then       '宾莹'
        |when title like     '%水晶%'         then     '水晶'
        |when title like     '%先锋%'         then     '先锋'
        |when title like     '%拉宾斯%'      then      '拉宾斯'
        |when title like     '%雷尼尔%'         then   '雷尼尔'
        |when title like     '%萨米脱%'         then   '萨米脱'
        |when title like     '%科迪亚%'      then      '科迪亚'
        |when title like     '%意大利早红%'       then '意大利早红'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '小龙虾' then
        |(case
        |when base_secondcate like    '%虾类制品%'     then '虾类制品'
        |when base_secondcate like    '%活虾%'      then   '活虾'
        |when base_secondcate like    '%虾仁%'     then    '虾仁'
        |when base_secondcate like    '%虾米%'  then       '虾米'
        |when base_secondcate like    '%冻虾%'    then     '冻虾'
        |else (
        |case
        |when title like    '%虾类制品%'     then '虾类制品'
        |when title like    '%活虾%'      then   '活虾'
        |when title like    '%虾仁%'     then    '虾仁'
        |when title like    '%虾米%'  then       '虾米'
        |when title like    '%冻虾%'    then     '冻虾'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate = '蟹类' then
        |(case
        |when base_secondcate like    '%鲜活%'     then '鲜活'
        |when base_secondcate like    '%冷冻%'      then   '冷冻'
        |when base_secondcate like    '%冷藏%'     then    '冷藏'
        |else (
        |case
        |when title like    '%鲜活%'     then '鲜活'
        |when title like    '%冷冻%'      then   '冷冻'
        |when title like    '%冷藏%'     then    '冷藏'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('大米','杂粮') then
        |(case
        |when base_secondcate like   '%杂粮礼盒%'     then '杂粮礼盒'
        |when base_secondcate like   '%糙米%'      then   '糙米'
        |when base_secondcate like   '%小米%'     then    '小米'
        |when base_secondcate like   '%黑豆%'     then    '黑豆'
        |when base_secondcate like   '%黄豆%'      then   '黄豆'
        |when base_secondcate like   '%黑米%'     then    '黑米'
        |when base_secondcate like   '%薏米%'     then    '薏米'
        |when base_secondcate like   '%糯米%'      then   '糯米'
        |else (
        |case
        |when title like   '%杂粮礼盒%'     then '杂粮礼盒'
        |when title like   '%糙米%'      then   '糙米'
        |when title like   '%小米%'     then    '小米'
        |when title like   '%黑豆%'     then    '黑豆'
        |when title like   '%黄豆%'      then   '黄豆'
        |when title like   '%黑米%'     then    '黑米'
        |when title like   '%薏米%'     then    '薏米'
        |when title like   '%糯米%'      then   '糯米'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('红茶') then
        |(case
        |when base_secondcate like  '%正山小种%'     then     '正山小种'
        |when base_secondcate like  '%金骏眉%'      then     '金骏眉'
        |when base_secondcate like  '%滇红%'        then     '滇红'
        |when base_secondcate like  '%祁门%'       then      '祁门'
        |when base_secondcate like  '%再加工茶%'   then       '再加工茶'
        |else (
        |case
        |when title like  '%正山小种%'     then     '正山小种'
        |when title like  '%金骏眉%'      then     '金骏眉'
        |when title like  '%滇红%'        then     '滇红'
        |when title like  '%祁门%'       then      '祁门'
        |when title like  '%再加工茶%'   then       '再加工茶'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('绿茶') then
        |(case
        |when base_secondcate like  '%洞庭碧螺春%'    then     '洞庭碧螺春'
        |when base_secondcate like  '%西湖龙井%'    then      '西湖龙井'
        |when base_secondcate like  '%信阳毛尖%'    then      '信阳毛尖'
        |when base_secondcate like  '%日照绿茶%'   then       '日照绿茶'
        |when base_secondcate like  '%龙井%'  then            '龙井'
        |when base_secondcate like  '%竹叶青%'    then        '竹叶青'
        |when base_secondcate like  '%雀舌%'    then          '雀舌'
        |when base_secondcate like  '%黄山毛峰%'    then      '黄山毛峰'
        |when base_secondcate like  '%毛峰%'   then           '毛峰'
        |else (
        |case
        |when title like  '%洞庭碧螺春%'    then     '洞庭碧螺春'
        |when title like  '%西湖龙井%'    then      '西湖龙井'
        |when title like  '%信阳毛尖%'    then      '信阳毛尖'
        |when title like  '%日照绿茶%'   then       '日照绿茶'
        |when title like  '%龙井%'  then            '龙井'
        |when title like  '%竹叶青%'    then        '竹叶青'
        |when title like  '%雀舌%'    then          '雀舌'
        |when title like  '%黄山毛峰%'    then      '黄山毛峰'
        |when title like  '%毛峰%'   then           '毛峰'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('黑茶') then
        |(case
        |when base_secondcate like  '%黑茶%'      then     '黑茶'
        |when base_secondcate like  '%普洱%'     then    '普洱茶'
        |when base_secondcate like  '%老青茶%'     then    '老青茶'
        |when base_secondcate like  '%六堡茶%'    then     '六堡茶'
        |when base_secondcate like  '%藏茶%'   then        '藏茶'
        |else (
        |case
        |when title like  '%黑茶%'      then     '黑茶'
        |when title like  '%普洱%'     then    '普洱茶'
        |when title like  '%老青茶%'     then    '老青茶'
        |when title like  '%六堡茶%'    then     '六堡茶'
        |when title like  '%藏茶%'   then        '藏茶'
        |else '其他' end
        |)end
        |)
        |when danpin_firstCate in ('蜜饯干果') then
        |(case
        |when base_secondcate like  '%梅类%'    then    '梅类'
        |when base_secondcate like  '%芒果干%'     then '芒果干'
        |when base_secondcate like  '%橄榄%'     then   '橄榄'
        |when base_secondcate like  '%葡萄干%'    then  '葡萄干'
        |when base_secondcate like  '%金桔%' then       '金桔'
        |when base_secondcate like  '%桃干%'    then    '桃干'
        |when base_secondcate like  '%草莓干%'     then '草莓干'
        |when base_secondcate like  '%无花果干%'     then '无花果干'
        |when base_secondcate like  '%李子%'    then    '李子'
        |when base_secondcate like  '%菠萝蜜%' then     '菠萝蜜'
        |else (
        |case
        |when title like  '%梅类%'    then    '梅类'
        |when title like  '%芒果干%'     then '芒果干'
        |when title like  '%橄榄%'     then   '橄榄'
        |when title like  '%葡萄干%'    then  '葡萄干'
        |when title like  '%金桔%' then       '金桔'
        |when title like  '%桃干%'    then    '桃干'
        |when title like  '%草莓干%'     then '草莓干'
        |when title like  '%无花果干%'     then '无花果干'
        |when title like  '%李子%'    then    '李子'
        |when title like  '%菠萝蜜%' then     '菠萝蜜'
        |else '其他' end
        |)end
        |)
        |
        |else '其他' end as danpin_secondCate
        |from
        |(select *,
        |case
        |when chandi like '湖南%'              then '湖南省'
        |when chandi like '云南%'              then '云南省'
        |when chandi like '河南%'              then '河南省'
        |when chandi like '江苏%'              then '江苏省'
        |when chandi like '甘肃%'              then '甘肃省'
        |when chandi like '黑龙江%'            then '黑龙江省'
        |when chandi like '天津%'              then '天津市'
        |when chandi like '广西壮族%'          then '广西壮族自治区'
        |when chandi like '陕西%'              then '陕西省'
        |when chandi like '宁夏回族%'          then '宁夏回族自治区'
        |when chandi like '湖北%'              then '湖北省'
        |when chandi like '重庆%'              then '重庆市'
        |when chandi like '江西%'              then '江西省'
        |when chandi like '四川%'              then '四川省'
        |when chandi like '山西%'              then '山西省'
        |when chandi like '青海%'              then '青海省'
        |when chandi like '福建%'              then '福建省'
        |when chandi like '河北%'              then '河北省'
        |when chandi like '海南%'              then '海南省'
        |when chandi like '新疆维吾尔%'        then '新疆维吾尔自治区'
        |when chandi like '广东%'              then '广东省'
        |when chandi like '北京%'              then '北京市'
        |when chandi like '辽宁%'              then '辽宁省'
        |when chandi like '安徽%'              then '安徽省'
        |when chandi like '吉林%'              then '吉林省'
        |when chandi like '浙江%'              then '浙江省'
        |when chandi like '西藏%'              then '西藏自治区'
        |when chandi like '内蒙古%'            then '内蒙古自治区'
        |when chandi like '上海%'              then '上海市'
        |when chandi like '山东%'              then '山东省'
        |when chandi like '贵州%'              then '贵州省'
        |else '-1' end as chandi_province,
        |case
        |when chandi1 rlike '中卫'      then '中卫市'
        |when chandi1 rlike '兰州'      then '兰州市'
        |when chandi1 rlike '嘉峪关'    then '嘉峪关市'
        |when chandi1 rlike '克拉玛依'  then '克拉玛依市'
        |when chandi1 rlike '西安'      then '西安市'
        |when chandi1 rlike '酒泉'      then '酒泉市'
        |when chandi1 rlike '伊犁哈萨克'then '伊犁哈萨克自治州'
        |when chandi1 rlike '咸阳'      then '咸阳市'
        |when chandi1 rlike '安康'      then '安康市'
        |when chandi1 rlike '汉中'      then '汉中市'
        |when chandi1 rlike '延安'      then '延安市'
        |when chandi1 rlike '陇南'      then '陇南市'
        |when chandi1 rlike '玉树藏'  then '玉树藏族自治州'
        |when chandi1 rlike '金昌'      then '金昌市'
        |when chandi1 rlike '庆阳'      then '庆阳市'
        |when chandi1 rlike '张掖'      then '张掖市'
        |when chandi1 rlike '渭南'      then '渭南市'
        |when chandi1 rlike '海南藏'  then '海南藏族自治州'
        |when chandi1 rlike '西宁'      then '西宁市'
        |when chandi1 rlike '天水'      then '天水市'
        |when chandi1 rlike '吐鲁番'    then '吐鲁番市'
        |when chandi1 rlike '喀什地'    then '喀什地区'
        |when chandi1 rlike '乌鲁木齐'  then '乌鲁木齐市'
        |when chandi1 rlike '巴音郭楞蒙古' then '巴音郭楞蒙古自治州'
        |when chandi1 rlike '辽源'      then '辽源市'
        |when chandi1 rlike '锦州'      then '锦州市'
        |when chandi1 rlike '大兴安岭地'then '大兴安岭地区'
        |when chandi1 rlike '松原'      then '松原市'
        |when chandi1 rlike '长春'      then '长春市'
        |when chandi1 rlike '伊春'      then '伊春市'
        |when chandi1 rlike '朝阳'      then '朝阳市'
        |when chandi1 rlike '阜新'      then '阜新市'
        |when chandi1 rlike '丹东'      then '丹东市'
        |when chandi1 rlike '双鸭山'    then '双鸭山市'
        |when chandi1 rlike '黑河'      then '黑河市'
        |when chandi1 rlike '大连'      then '大连市'
        |when chandi1 rlike '哈尔滨'    then '哈尔滨市'
        |when chandi1 rlike '四平'      then '四平市'
        |when chandi1 rlike '佳木斯'    then '佳木斯市'
        |when chandi1 rlike '辽阳'      then '辽阳市'
        |when chandi1 rlike '鸡西'      then '鸡西市'
        |when chandi1 rlike '本溪'      then '本溪市'
        |when chandi1 rlike '白山'      then '白山市'
        |when chandi1 rlike '七台河'    then '七台河市'
        |when chandi1 rlike '绥化'      then '绥化市'
        |when chandi1 rlike '吉林'      then '吉林市'
        |when chandi1 rlike '通化'      then '通化市'
        |when chandi1 rlike '遂宁'      then '遂宁市'
        |when chandi1 rlike '长治'      then '长治市'
        |when chandi1 rlike '达州'      then '达州市'
        |when chandi1 rlike '廊坊'      then '廊坊市'
        |when chandi1 rlike '六盘水'    then '六盘水市'
        |when chandi1 rlike '阳泉'      then '阳泉市'
        |when chandi1 rlike '毕节'      then '毕节市'
        |when chandi1 rlike '成都'      then '成都市'
        |when chandi1 rlike '锡林郭勒盟' then '锡林郭勒盟'
        |when chandi1 rlike '衡水'      then '衡水市'
        |when chandi1 rlike '攀枝花'    then '攀枝花市'
        |when chandi1 rlike '凉山彝'  then '凉山彝族自治州'
        |when chandi1 rlike '绵阳'      then '绵阳市'
        |when chandi1 rlike '昆明'      then '昆明市'
        |when chandi1 rlike '雅安'      then '雅安市'
        |when chandi1 rlike '广安'      then '广安市'
        |when chandi1 rlike '邯郸'      then '邯郸市'
        |when chandi1 rlike '天津'      then '天津市'
        |when chandi1 rlike '吕梁'      then '吕梁市'
        |when chandi1 rlike '宜宾'      then '宜宾市'
        |when chandi1 rlike '大同'      then '大同市'
        |when chandi1 rlike '儋州'      then '儋州市'
        |when chandi1 rlike '清远'      then '清远市'
        |when chandi1 rlike '江门'      then '江门市'
        |when chandi1 rlike '河源'      then '河源市'
        |when chandi1 rlike '三亚'      then '三亚市'
        |when chandi1 rlike '玉林'      then '玉林市'
        |when chandi1 rlike '中山'      then '中山市'
        |when chandi1 rlike '贺州'      then '贺州市'
        |when chandi1 rlike '来宾'      then '来宾市'
        |when chandi1 rlike '柳州'      then '柳州市'
        |when chandi1 rlike '佛山'      then '佛山市'
        |when chandi1 rlike '珠海'      then '珠海市'
        |when chandi1 rlike '汕尾'      then '汕尾市'
        |when chandi1 rlike '韶关'      then '韶关市'
        |when chandi1 rlike '东莞'      then '东莞市'
        |when chandi1 rlike '肇庆'      then '肇庆市'
        |when chandi1 rlike '惠州'      then '惠州市'
        |when chandi1 rlike '汕头'      then '汕头市'
        |when chandi1 rlike '河池'      then '河池市'
        |when chandi1 rlike '百色'      then '百色市'
        |when chandi1 rlike '黄冈'      then '黄冈市'
        |when chandi1 rlike '娄底'      then '娄底市'
        |when chandi1 rlike '湘潭'      then '湘潭市'
        |when chandi1 rlike '岳阳'      then '岳阳市'
        |when chandi1 rlike '常德'      then '常德市'
        |when chandi1 rlike '黄石'      then '黄石市'
        |when chandi1 rlike '荆门'      then '荆门市'
        |when chandi1 rlike '武汉'      then '武汉市'
        |when chandi1 rlike '宜昌'      then '宜昌市'
        |when chandi1 rlike '张家界'    then '张家界市'
        |when chandi1 rlike '咸宁'      then '咸宁市'
        |when chandi1 rlike '驻马店'    then '驻马店市'
        |when chandi1 rlike '随州'      then '随州市'
        |when chandi1 rlike '商丘'      then '商丘市'
        |when chandi1 rlike '洛阳'      then '洛阳市'
        |when chandi1 rlike '永州'      then '永州市'
        |when chandi1 rlike '安阳'      then '安阳市'
        |when chandi1 rlike '鹤壁'      then '鹤壁市'
        |when chandi1 rlike '益阳'      then '益阳市'
        |when chandi1 rlike '平顶山'    then '平顶山市'
        |when chandi1 rlike '荆州'      then '荆州市'
        |when chandi1 rlike '株洲'      then '株洲市'
        |when chandi1 rlike '信阳'      then '信阳市'
        |when chandi1 rlike '南阳'      then '南阳市'
        |when chandi1 rlike '濮阳'      then '濮阳市'
        |when chandi1 rlike '开封'      then '开封市'
        |when chandi1 rlike '白银'      then '白银市'
        |when chandi1 rlike '平凉'      then '平凉市'
        |when chandi1 rlike '铜川'      then '铜川市'
        |when chandi1 rlike '阿克苏地'  then '阿克苏地区'
        |when chandi1 rlike '哈密'      then '哈密市'
        |when chandi1 rlike '商洛'      then '商洛市'
        |when chandi1 rlike '宝鸡'      then '宝鸡市'
        |when chandi1 rlike '石嘴山'    then '石嘴山市'
        |when chandi1 rlike '甘南藏'  then '甘南藏族自治州'
        |when chandi1 rlike '武威'      then '武威市'
        |when chandi1 rlike '沈阳'      then '沈阳市'
        |when chandi1 rlike '盘锦'      then '盘锦市'
        |when chandi1 rlike '铁岭'      then '铁岭市'
        |when chandi1 rlike '齐齐哈尔'  then '齐齐哈尔市'
        |when chandi1 rlike '牡丹江'    then '牡丹江市'
        |when chandi1 rlike '营口'      then '营口市'
        |when chandi1 rlike '大庆'      then '大庆市'
        |when chandi1 rlike '抚顺'      then '抚顺市'
        |when chandi1 rlike '运城'      then '运城市'
        |when chandi1 rlike '沧州'      then '沧州市'
        |when chandi1 rlike '黔南布依族苗' then '黔南布依族苗族自治州'
        |when chandi1 rlike '红河哈尼族彝' then '红河哈尼族彝族自治州'
        |when chandi1 rlike '楚雄彝'  then '楚雄彝族自治州'
        |when chandi1 rlike '门头沟'    then '门头沟区'
        |when chandi1 rlike '呼和浩特'  then '呼和浩特市'
        |when chandi1 rlike '保定'      then '保定市'
        |when chandi1 rlike '眉山'      then '眉山市'
        |when chandi1 rlike '重庆'      then '重庆市'
        |when chandi1 rlike '铜仁'      then '铜仁市'
        |when chandi1 rlike '自贡'      then '自贡市'
        |when chandi1 rlike '巴中'      then '巴中市'
        |when chandi1 rlike '广元'      then '广元市'
        |when chandi1 rlike '晋城'      then '晋城市'
        |when chandi1 rlike '揭阳'      then '揭阳市'
        |when chandi1 rlike '钦州'      then '钦州市'
        |when chandi1 rlike '海口'      then '海口市'
        |when chandi1 rlike '湛江'      then '湛江市'
        |when chandi1 rlike '深圳'      then '深圳市'
        |when chandi1 rlike '潮州'      then '潮州市'
        |when chandi1 rlike '三沙'      then '三沙市'
        |when chandi1 rlike '云浮'      then '云浮市'
        |when chandi1 rlike '茂名'      then '茂名市'
        |when chandi1 rlike '周口'      then '周口市'
        |when chandi1 rlike '郑州'      then '郑州市'
        |when chandi1 rlike '许昌'      then '许昌市'
        |when chandi1 rlike '三门峡'    then '三门峡市'
        |when chandi1 rlike '怀化'      then '怀化市'
        |when chandi1 rlike '襄阳'      then '襄阳市'
        |when chandi1 rlike '衡阳'      then '衡阳市'
        |when chandi1 rlike '漯河'      then '漯河市'
        |when chandi1 rlike '十堰'      then '十堰市'
        |when chandi1 rlike '郴州'      then '郴州市'
        |when chandi1 rlike '马鞍山'    then '马鞍山市'
        |when chandi1 rlike '烟台'      then '烟台市'
        |when chandi1 rlike '九江'      then '九江市'
        |when chandi1 rlike '龙岩'      then '龙岩市'
        |when chandi1 rlike '聊城'      then '聊城市'
        |when chandi1 rlike '济宁'      then '济宁市'
        |when chandi1 rlike '常州'      then '常州市'
        |when chandi1 rlike '宁德'      then '宁德市'
        |when chandi1 rlike '德州'      then '德州市'
        |when chandi1 rlike '临沂'      then '临沂市'
        |when chandi1 rlike '青岛'      then '青岛市'
        |when chandi1 rlike '新余'      then '新余市'
        |when chandi1 rlike '厦门'      then '厦门市'
        |when chandi1 rlike '滁州'      then '滁州市'
        |when chandi1 rlike '苏州'      then '苏州市'
        |when chandi1 rlike '南昌'      then '南昌市'
        |when chandi1 rlike '滨州'      then '滨州市'
        |when chandi1 rlike '南平'      then '南平市'
        |when chandi1 rlike '宁波'      then '宁波市'
        |when chandi1 rlike '淄博'      then '淄博市'
        |when chandi1 rlike '菏泽'      then '菏泽市'
        |when chandi1 rlike '徐州'      then '徐州市'
        |when chandi1 rlike '景德镇'    then '景德镇市'
        |when chandi1 rlike '莆田'      then '莆田市'
        |when chandi1 rlike '台州'      then '台州市'
        |when chandi1 rlike '福州'      then '福州市'
        |when chandi1 rlike '漳州'      then '漳州市'
        |when chandi1 rlike '上饶'      then '上饶市'
        |when chandi1 rlike '济南'      then '济南市'
        |when chandi1 rlike '泉州'      then '泉州市'
        |when chandi1 rlike '嘉兴'      then '嘉兴市'
        |when chandi1 rlike '连云港'    then '连云港市'
        |when chandi1 rlike '金华'      then '金华市'
        |when chandi1 rlike '淮北'      then '淮北市'
        |when chandi1 rlike '上海'      then '上海市'
        |when chandi1 rlike '威海'      then '威海市'
        |when chandi1 rlike '东营'      then '东营市'
        |when chandi1 rlike '蚌埠'      then '蚌埠市'
        |when chandi1 rlike '日照'      then '日照市'
        |when chandi1 rlike '淮安'      then '淮安市'
        |when chandi1 rlike '黄山'      then '黄山市'
        |when chandi1 rlike '焦作'      then '焦作市'
        |when chandi1 rlike '恩施土家族苗' then '恩施土家族苗族自治州'
        |when chandi1 rlike '邵阳'      then '邵阳市'
        |when chandi1 rlike '海东'      then '海东市'
        |when chandi1 rlike '定西'      then '定西市'
        |when chandi1 rlike '黄南藏'  then '黄南藏族自治州'
        |when chandi1 rlike '固原'      then '固原市'
        |when chandi1 rlike '新乡'      then '新乡市'
        |when chandi1 rlike '鄂州'      then '鄂州市'
        |when chandi1 rlike '榆林'      then '榆林市'
        |when chandi1 rlike '银川'      then '银川市'
        |when chandi1 rlike '海西蒙古族藏' then '海西蒙古族藏族自治州'
        |when chandi1 rlike '塔城地'    then '塔城地区'
        |when chandi1 rlike '鞍山'      then '鞍山市'
        |when chandi1 rlike '延边朝鲜' then '延边朝鲜族自治州'
        |when chandi1 rlike '阿勒泰地'  then '阿勒泰地区'
        |when chandi1 rlike '博尔塔拉蒙古' then '博尔塔拉蒙古自治州'
        |when chandi1 rlike '吴忠'      then '吴忠市'
        |when chandi1 rlike '果洛藏'  then '果洛藏族自治州'
        |when chandi1 rlike '内江'      then '内江市'
        |when chandi1 rlike '泸州'      then '泸州市'
        |when chandi1 rlike '朔州'      then '朔州市'
        |when chandi1 rlike '张家口'    then '张家口市'
        |when chandi1 rlike '玉溪'      then '玉溪市'
        |when chandi1 rlike '北京'      then '北京市'
        |when chandi1 rlike '邢台'      then '邢台市'
        |when chandi1 rlike '兴安盟'    then '兴安盟'
        |when chandi1 rlike '临汾'      then '临汾市'
        |when chandi1 rlike '丽江'      then '丽江市'
        |when chandi1 rlike '梅州'      then '梅州市'
        |when chandi1 rlike '贵港'      then '贵港市'
        |when chandi1 rlike '崇左'      then '崇左市'
        |when chandi1 rlike '阳江'      then '阳江市'
        |when chandi1 rlike '广州'      then '广州市'
        |when chandi1 rlike '南宁'      then '南宁市'
        |when chandi1 rlike '孝感'      then '孝感市'
        |when chandi1 rlike '长沙'      then '长沙市'
        |when chandi1 rlike '昌吉回'  then '昌吉回族自治州'
        |when chandi1 rlike '北海'      then '北海市'
        |when chandi1 rlike '桂林'      then '桂林市'
        |when chandi1 rlike '梧州'      then '梧州市'
        |when chandi1 rlike '葫芦岛'    then '葫芦岛市'
        |when chandi1 rlike '唐山'      then '唐山市'
        |when chandi1 rlike '资阳'      then '资阳市'
        |when chandi1 rlike '那曲'      then '那曲市'
        |when chandi1 rlike '秦皇岛'    then '秦皇岛市'
        |when chandi1 rlike '鄂尔多斯'  then '鄂尔多斯市'
        |when chandi1 rlike '承德'      then '承德市'
        |when chandi1 rlike '昭通'      then '昭通市'
        |when chandi1 rlike '忻州'      then '忻州市'
        |when chandi1 rlike '安顺'      then '安顺市'
        |when chandi1 rlike '湘西土家族苗' then '湘西土家族苗族自治州'
        |when chandi1 rlike '白城'      then '白城市'
        |when chandi1 rlike '保山'      then '保山市'
        |when chandi1 rlike '乐山'      then '乐山市'
        |when chandi1 rlike '临沧'      then '临沧市'
        |when chandi1 rlike '南充'      then '南充市'
        |when chandi1 rlike '甘孜藏'  then '甘孜藏族自治州'
        |when chandi1 rlike '和田地'    then '和田地区'
        |when chandi1 rlike '太原'      then '太原市'
        |when chandi1 rlike '静海'      then '静海区'
        |when chandi1 rlike '呼伦贝尔'  then '呼伦贝尔市'
        |when chandi1 rlike '山南'      then '山南市'
        |when chandi1 rlike '合川'      then '合川区'
        |when chandi1 rlike '黔东南苗族侗' then '黔东南苗族侗族自治州'
        |when chandi1 rlike '赤峰'      then '赤峰市'
        |when chandi1 rlike '贵阳'      then '贵阳市'
        |when chandi1 rlike '防城港'    then '防城港市'
        |when chandi1 rlike '鹤岗'      then '鹤岗市'
        |when chandi1 rlike '石家庄'    then '石家庄市'
        |when chandi1 rlike '德阳'      then '德阳市'
        |when chandi1 rlike '遵义'      then '遵义市'
        |when chandi1 rlike '海北藏'  then '海北藏族自治州'
        |when chandi1 rlike '晋中'      then '晋中市'
        |when chandi1 rlike '阿拉善盟'  then '阿拉善盟'
        |when chandi1 rlike '涪陵'      then '涪陵区'
        |when chandi1 rlike '日喀则'    then '日喀则市'
        |when chandi1 rlike '酉阳土家族苗' then '酉阳土家族苗族自治县'
        |when chandi1 rlike '怒江傈僳' then '怒江傈僳族自治州'
        |when chandi1 rlike '乌海'      then '乌海市'
        |when chandi1 rlike '巴彦淖尔'  then '巴彦淖尔市'
        |when chandi1 rlike '南京'      then '南京市'
        |when chandi1 rlike '三明'      then '三明市'
        |when chandi1 rlike '宣城'      then '宣城市'
        |when chandi1 rlike '湖州'      then '湖州市'
        |when chandi1 rlike '枣庄'      then '枣庄市'
        |when chandi1 rlike '抚州'      then '抚州市'
        |when chandi1 rlike '宿州'      then '宿州市'
        |when chandi1 rlike '安庆'      then '安庆市'
        |when chandi1 rlike '衢州'      then '衢州市'
        |when chandi1 rlike '西双版纳傣' then '西双版纳傣族自治州'
        |when chandi1 rlike '绍兴'      then '绍兴市'
        |when chandi1 rlike '萍乡'      then '萍乡市'
        |when chandi1 rlike '吉安'      then '吉安市'
        |when chandi1 rlike '泰安'      then '泰安市'
        |when chandi1 rlike '丽水'      then '丽水市'
        |when chandi1 rlike '宿迁'      then '宿迁市'
        |when chandi1 rlike '临夏回'  then '临夏回族自治州'
        |when chandi1 rlike '北辰'      then '北辰区'
        |when chandi1 rlike '武隆县'    then '武隆县'
        |when chandi1 rlike '拉萨'      then '拉萨市'
        |when chandi1 rlike '南川'      then '南川区'
        |when chandi1 rlike '红桥'      then '红桥区'
        |when chandi1 rlike '扬州'      then '扬州市'
        |when chandi1 rlike '温州'      then '温州市'
        |when chandi1 rlike '阜阳'      then '阜阳市'
        |when chandi1 rlike '亳州'      then '亳州市'
        |when chandi1 rlike '包头'      then '包头市'
        |when chandi1 rlike '巫溪县'    then '巫溪县'
        |when chandi1 rlike '普洱'      then '普洱市'
        |when chandi1 rlike '那曲地'    then '那曲地区'
        |when chandi1 rlike '盐城'      then '盐城市'
        |when chandi1 rlike '合肥'      then '合肥市'
        |when chandi1 rlike '泰州'      then '泰州市'
        |when chandi1 rlike '淮南'      then '淮南市'
        |when chandi1 rlike '曲靖'      then '曲靖市'
        |when chandi1 rlike '铜陵'      then '铜陵市'
        |when chandi1 rlike '赣州'      then '赣州市'
        |when chandi1 rlike '静安'      then '静安区'
        |when chandi1 rlike '通辽'      then '通辽市'
        |when chandi1 rlike '芜湖'      then '芜湖市'
        |when chandi1 rlike '杭州'      then '杭州市'
        |when chandi1 rlike '武清'      then '武清区'
        |when chandi1 rlike '南岸'      then '南岸区'
        |when chandi1 rlike '海淀'      then '海淀区'
        |when chandi1 rlike '九龙坡'    then '九龙坡区'
        |when chandi1 rlike '镇江'      then '镇江市'
        |when chandi1 rlike '潍坊'      then '潍坊市'
        |when chandi1 rlike '长寿'      then '长寿区'
        |when chandi1 rlike '无锡'      then '无锡市'
        |when chandi1 rlike '池州'      then '池州市'
        |when chandi1 rlike '鹰潭'      then '鹰潭市'
        |when chandi1 rlike '六安'      then '六安市'
        |when chandi1 rlike '宜春'      then '宜春市'
        |when chandi1 rlike '南通'      then '南通市'
        |when chandi1 rlike '荣昌'      then '荣昌区'
        |when chandi1 rlike '昌都'      then '昌都市'
        |when chandi1 rlike '大理白'  then '大理白族自治州'
        |when chandi1 rlike '河西'      then '河西区'
        |when chandi1 rlike '舟山'      then '舟山市'
        |when chandi1 rlike '克孜勒苏柯尔克孜' then '克孜勒苏柯尔克孜自治州'
        |when chandi1 rlike '秀山土家族苗'then '秀山土家族苗族自治县'
        |when chandi1 rlike '黔西南布依族苗' then '黔西南布依族苗族自治州'
        |when chandi1 rlike '璧山'      then '璧山区'
        |when chandi1 rlike '东丽'      then '东丽区'
        |when chandi1 rlike '密云'      then '密云区'
        |when chandi1 rlike '沙坪坝'    then '沙坪坝区'
        |when chandi1 rlike '北碚'      then '北碚区'
        |when chandi1 rlike '延庆'      then '延庆区'
        |when chandi1 rlike '乌兰察布'  then '乌兰察布市'
        |when chandi1 rlike '阿里地'    then '阿里地区'
        |when chandi1 rlike '房山'      then '房山区'
        |when chandi1 rlike '蓟州'      then '蓟州区'
        |when chandi1 rlike '昌平'      then '昌平区'
        |when chandi1 rlike '渝北'      then '渝北区'
        |when chandi1 rlike '巴南'      then '巴南区'
        |when chandi1 rlike '顺义'      then '顺义区'
        |when chandi1 rlike '城口县'    then '城口县'
        |when chandi1 rlike '潼南'      then '潼南区'
        |when chandi1 rlike '垫江县'    then '垫江县'
        |when chandi1 rlike '江津'      then '江津区'
        |when chandi1 rlike '怀柔'      then '怀柔区'
        |when chandi1 rlike '平谷'      then '平谷区'
        |when chandi1 rlike '忠县'      then '忠县'
        |when chandi1 rlike '西青'      then '西青区'
        |when chandi1 rlike '大足'      then '大足区'
        |when chandi1 rlike '林芝'      then '林芝市'
        |when chandi1 rlike '南开'      then '南开区'
        |when chandi1 rlike '通州'      then '通州区'
        |when chandi1 rlike '文山壮族苗' then '文山壮族苗族自治州'
        |when chandi1 rlike '滨海新'    then '滨海新区'
        |when chandi1 rlike '大兴'      then '大兴区'
        |when chandi1 rlike '德宏傣族景颇' then '德宏傣族景颇族自治州'
        |when chandi1 rlike '綦江'      then '綦江区'
        |when chandi1 rlike '丰台'      then '丰台区'
        |when chandi1 rlike '渝中'      then '渝中区'
        |when chandi1 rlike '黔江'      then '黔江区'
        |when chandi1 rlike '东城'      then '东城区'
        |when chandi1 rlike '云阳县'    then '云阳县'
        |when chandi1 rlike '彭水苗族土家'   then '彭水苗族土家族自治县'
        |when chandi1 rlike '巫山县'    then '巫山县'
        |when chandi1 rlike '河东'      then '河东区'
        |when chandi1 rlike '宝坻'      then '宝坻区'
        |when chandi1 rlike '铜梁'      then '铜梁区'
        |when chandi1 rlike '津南'      then '津南区'
        |when chandi1 rlike '丰都县'    then '丰都县'
        |when chandi1 rlike '阿坝藏族羌' then '阿坝藏族羌族自治州'
        |when chandi1 rlike '西城'      then '西城区'
        |when chandi1 rlike '宁河'      then '宁河区'
        |when chandi1 rlike '大渡口'    then '大渡口区'
        |when chandi1 rlike '永川'      then '永川区'
        |when chandi1 rlike '万州'      then '万州区'
        |when chandi1 rlike '石景山'    then '石景山区'
        |when chandi1 rlike '河北'      then '河北区'
        |when chandi1 rlike '石柱土家' then '石柱土家族自治县'
        |when chandi1 rlike '江北'      then '江北区'
        |when chandi1 rlike '开州'      then '开州区'
        |when chandi1 rlike '和平'      then '和平区'
        |when chandi1 rlike '奉节县'    then '奉节县'
        |when chandi1 rlike '迪庆藏'  then '迪庆藏族自治州'
        |when chandi1 rlike '梁平县'    then '梁平县'
        |when chandi1 rlike '朝阳'      then '朝阳区'
        |when chandi1 rlike '普陀'      then '普陀区'
        |when chandi1 rlike '长宁'      then '长宁区'
        |when chandi1 rlike '虹口'      then '虹口区'
        |when chandi1 rlike '闵行'      then '闵行区'
        |when chandi1 rlike '松江'      then '松江区'
        |when chandi1 rlike '崇明'      then '崇明区'
        |when chandi1 rlike '徐汇'      then '徐汇区'
        |when chandi1 rlike '浦东新'    then '浦东新区'
        |when chandi1 rlike '黄浦'      then '黄浦区'
        |when chandi1 rlike '奉贤'      then '奉贤区'
        |when chandi1 rlike '宝山'      then '宝山区'
        |when chandi1 rlike '金山'      then '金山区'
        |when chandi1 rlike '嘉定'      then '嘉定区'
        |when chandi1 rlike '杨浦'      then '杨浦区'
        |when chandi1 rlike '青浦'      then '青浦区'
        |when chandi1 rlike '沈抚新'    then '沈抚新区'
        |else '-1' end as chandi_city,
        |
        |--品类
        |case when platformId in (9,10) then
        |(case
        |when  danpin_firstCate in ('苹果','橙子/橘子') then json_parse(base_info,'水果种类')
        |when  danpin_firstCate in ('车厘子/樱桃') then json_parse(base_info,'食品品种')
        | when  danpin_firstCate= '小龙虾' then  json_parse(base_info,'食品工艺')
        | when  danpin_firstCate= '蟹类' then  json_parse(base_info,'特产品类')
        | when  danpin_firstCate in ('红茶','绿茶','黑茶') then  json_parse(base_info,'茶种类')
        | else '-1' end)
        |
        |when platformId not in (9,10,5,3,20) then
        |(case
        |when  danpin_firstCate in ('苹果','橙子/橘子','车厘子/樱桃','大米','杂粮','小龙虾','蟹类','蜜饯干果') then json_parse(base_info,'产品名称')
        |when  danpin_firstCate in ('红茶','绿茶','黑茶') then json_parse(base_info,'品名')
        | else '-1' end)
        | else '-1' end as base_secondcate
        |from
        |--品类清洗完成
        |(select
        |*,
        |case when platformId in (9,10) then
        |(case
        |when categoryId='50050732' then '苹果'
        |when categoryId in ('50280002','123220003') then '橙子/橘子'
        |when categoryId in ('50050730') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('50016794','50050706','50050707','123220002') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('50016792',
        |'50016800',
        |'50050708',
        |'50050710',
        |'123188006',
        |'123200006',
        |'123224004',
        |'127692021',
        |'50050709',
        |'123222005')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') or categoryId in ('50598001') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when platformId in (5) then
        |(case
        |when categoryId='13554' then '苹果'
        |when categoryId in ('13555','13567') then '橙子/橘子'
        |when categoryId in ('13557') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('12242') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('12243')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') or categoryId in ('12204') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId in (3) then
        |(case
        |when categoryId='503926' then '苹果'
        |when categoryId='503923' and title rlike '橘' then '橙子/橘子'
        |when detailCategoryName in ('车厘子') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('503931') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('503934')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when thirdCategoryId in ('100210808') or categoryId in ('500379') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId in (20) then
        |(case
        |when categoryId in ('8269','2447','171','7718') then '苹果'
        |when categoryId in ('5122','橙','1802','5123','桔子','165') then '橙子/橘子'
        |when categoryId in ('1212','车厘子樱桃','161','8249') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when categoryId in ('虾类','175','8195') and title rlike '小龙虾' then '小龙虾'
        |when categoryId in ('蟹类','176','8196')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when categoryId in ('6610','6611','6612','5272') or subCategoryName in ('普洱','黑茶') then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |when  platformId not in (9,10,5,3,20) then
        |(case
        |when tbCategoryId in ('126198303','126198306','126198308') then '苹果'
        |when (categoryId in ('txd_10000040') and title rlike '橙') or (categoryId in ('txd_10000040') and title rlike '橘')
        | or tbCategoryId in ('127532102') or tbCategoryId in ('127532101') then '橙子/橘子'
        |when tbCategoryId in ('126198318') then '车厘子/樱桃'
        |when thirdCategoryId in ('100210203') then '大米'
        |when thirdCategoryId in ('100210205') then '杂粮'
        |when tbCategoryId in ('126198437',
        |'txd_10012075',
        |'127530162',
        |'127530159',
        |'127530160',
        |'127530161',
        |'127526171') and title rlike '小龙虾' then '小龙虾'
        |when tbCategoryId in ('127532156',
        |'127530102',
        |'127528157',
        |'127526172',
        |'126198448',
        |'126198436')  then '蟹类'
        |when thirdCategoryId in ('100210804') then '红茶'
        |when thirdCategoryId in ('100210803') then '绿茶'
        |when tbCategoryId in ('126198766') and (title rlike '黑茶'
        |or title rlike '茯茶'
        |or title rlike '千两茶'
        |or title rlike '黑砖茶'
        |or title rlike '三尖'
        |or title rlike '青砖茶'
        |or title rlike '藏茶'
        |or title rlike '边茶'
        |or title rlike '安茶'
        |or title rlike '普洱熟茶'
        |or title rlike '六堡茶' ) then '黑茶'
        |when thirdCategoryId in ('100210105') then '蜜饯干果'
        |else '-1' end
        |)
        |else '-1' end as danpin_firstCate,
        |--产地
        |case
        |when platformid in (9,10) then json_parse(base_info,'省份')
        |when platformid in (5,3,20) then '-1'
        |else json_parse(base_info,'产地') end as chandi,
        |case when platformid in (9,10) then json_parse(base_info,'城市')
        |when platformid in (5,3,20) then '-1'
        |else json_parse(base_info,'产地') end as chandi1
        |from
        |o2o_ddp_ODS))
        |
          """.stripMargin).where("danpin_firstCate!='-1'").dropDuplicates("timeStamp","platformId","good_id")

    result.repartition(5).write.orc(s"s3a://o2o-dataproces-group/han_congcong/Dadanpin/result0407_puer/${year}_pinduoduo")
  }

}
